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.
Creating a simple stored procedure
SELECT statement returns a list of products from the
products table in the BikeStores sample database:
SELECT product_name, list_price FROM production.products ORDER BY product_name;
To create a stored procedure that wraps this query, you use the
CREATE PROCEDURE statement as follows:
CREATE PROCEDURE uspProductList AS BEGIN SELECT product_name, list_price FROM production.products ORDER BY product_name; END;
In this syntax:
uspProductListis the name of the stored procedure.
ASkeyword separates the heading and the body of the stored procedure.
- If the stored procedure has one statement, the
ENDkeywords surrounding the statement are optional. However, it is a good practice to include them to make the code clear.
Note that in addition to the
CREATE PROCEDURE keywords, you can use the
CREATE PROC keywords to make the statement shorter.
To compile this stored procedure, you execute it as a normal SQL statement in SQL Server Management Studio as shown in the following picture:
If everything is correct, then you will see the following message:
Commands completed successfully.
It means that the stored procedure has been successfully compiled and saved into the database catalog.
You can find the stored procedure in the Object Explorer, under Programmability > Stored Procedures as shown below:
Sometimes, you need to click the Refresh button to manually update the database objects in the Object Explorer.
Executing a stored procedure
To execute a stored procedure, you use the
EXEC statement followed by the name of the stored procedure:
sp_name is the name of the stored procedure that you want to execute.
For example, to execute the
uspProductList stored procedure, you use the following statement:
The stored procedure returns the following output:
Modifying a stored procedure
To modify an existing stored procedure, you use the
ALTER PROCEDURE statement.
First, open the stored procedure to view its contents by right-clicking the stored procedure name and select Modify menu item:
Second, change the body of the stored procedure by sorting the products by list prices instead of product names:
ALTER PROCEDURE uspProductList AS BEGIN SELECT product_name, list_price FROM production.products ORDER BY list_price END;
Third, click the Execute button, SQL Server modifies the stored procedure and returns the following output:
Commands completed successfully.
Now, if you execute the stored procedure again, you will see the changes taking effect:
The following shows the partial output:
Deleting a stored procedure
To delete a stored procedure, you use the
DROP PROCEDURE or
DROP PROC statement:
DROP PROCEDURE sp_name;
DROP PROC sp_name;
sp_name is the name of the stored procedure that you want to delete.
For example, to remove the
uspProductList stored procedure, you execute the following statement:
DROP PROCEDURE uspProductList;
In this tutorial, you have learned how to manage SQL Server stored procedures including creating, executing, modifying, and deleting stored procedures.