Day 74 – SQL CREATE PROCEDURE Keyword
In SQL, the CREATE PROCEDURE statement is used to create a stored procedure, which is a set of SQL statements that can be stored in a database and executed later as a single unit. Stored procedures are commonly used to encapsulate complex SQL logic that is reused across multiple database applications.
The basic syntax for creating a stored procedure is as follows:
CREATE PROCEDURE procedure_name
[ ( parameter [ datatype ] [ = default ] [, ...] ) ]
AS
BEGIN
SQL statement(s);
END;
Here, procedure_name is the name of the stored procedure, parameter is an optional parameter that can be passed to the stored procedure, datatype is the data type of the parameter, and default is the default value of the parameter.
For example, to create a stored procedure that retrieves all orders for a given customer, the SQL statement would be:
CREATE PROCEDURE sp_GetCustomerOrders
@CustomerID int
AS
BEGIN
SELECT *
FROM Orders
WHERE CustomerID = @CustomerID;
END;
In this example, the stored procedure is named “sp_GetCustomerOrders” and accepts a single parameter, @CustomerID. The stored procedure uses a SELECT statement to retrieve all orders from the “Orders” table where the CustomerID column matches the @CustomerID parameter.
Once the stored procedure is created, it can be executed by calling its name and passing the required parameter(s), like so:
EXEC sp_GetCustomerOrders @CustomerID = 1234;
This will execute the “sp_GetCustomerOrders” stored procedure with a value of 1234 for the @CustomerID parameter, returning all orders for the specified customer.