A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
EXECUTE permissions on a stored procedure without giving them direct access to the underlying tables. This allows you to control and audit data access very precisely.EXECUTE command.The syntax for creating a stored procedure varies slightly between database systems.
CREATE PROCEDURE procedure_name AS sql_statement GO;
DELIMITER // CREATE PROCEDURE procedure_name() BEGIN sql_statement; END // DELIMITER ;
EXEC procedure_name;
For these examples, we'll assume we have the following Customers and Orders tables.
Customers Table:
| CustomerID | CustomerName | Country |
|---|---|---|
| 1 | Alfreds Futterkiste | Germany |
| 2 | Ana Trujillo Emparedados | Mexico |
| 3 | Antonio Moreno Taquería | Mexico |
Orders Table:
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 10308 | 2 | 1996-09-18 |
| 10310 | 1 | 1996-09-20 |
The following SQL statement creates a stored procedure named SelectAllCustomers that selects all records from the Customers table:
CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers; GO;
You can then execute this procedure like this:
EXEC SelectAllCustomers;
The following SQL statement creates a stored procedure that selects customers from a particular country, which is passed as a parameter.
CREATE PROCEDURE SelectCustomersByCountry @Country nvarchar(30) AS SELECT * FROM Customers WHERE Country = @Country; GO;-- Execute the procedure for the country "Mexico" EXEC SelectCustomersByCountry @Country = 'Mexico';