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;
Here, 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.
The AS keyword is used to begin the definition of the stored procedure, and the BEGIN and 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.