Day 64 – SQL CREATE PROCEDURE
SQL CREATE PROCEDURE keyword is used to create a stored procedure in a database. A stored procedure is a pre-compiled set of SQL statements that can be executed by a database server. Stored procedures are often used to encapsulate complex business logic, to provide an interface to legacy code, or to improve performance by reducing the number of network round-trips required to execute a series of related SQL statements.
The basic syntax for using the CREATE PROCEDURE keyword is as follows:
CREATE PROCEDURE procedure_name [ ( parameter_name data_type [ = default_value ] ) [ , ... ] ] AS BEGIN sql_statement; [ sql_statement; ... ] END;
procedure_name is the name of the new stored procedure, and
parameter_name is an optional parameter that can be used to pass values into the stored procedure. The
data_type specifies the data type of the parameter, and
default_value is an optional default value for the parameter.
AS keyword is used to begin the definition of the stored procedure, and the
END keywords are used to define the body of the stored procedure, which can contain one or more SQL statements.
For example, to create a stored procedure named “get_student_info” that retrieves information about a student from a database, the SQL statement would be:
CREATE PROCEDURE get_student_info @student_id INT AS BEGIN SELECT * FROM Students WHERE StudentID = @student_id; END;
This would create a new stored procedure named “get_student_info” that accepts a single parameter,
@student_id, and returns a result set containing all columns from the “Students” table where the “StudentID” column matches the specified value.
Once a stored procedure has been created, it can be executed by calling its name from another SQL statement or from an application program that connects to the database.