SQL Tutorial

Stored Procedures

Creating Stored Procedures

Stored procedures are created with the CREATE PROCEDURE statement. The syntax is shown below.

CREATE PROCEDURE procedure_name
@param data_type = default_value,
@param data_type = default_value,
@param data_type = default_value
AS
-- statements for procedure here

The demo below shows a script that creates a simplified version of a SalesByCategory stored procedure that ships with SQL Server 2000's Northwind sample database.

CREATE PROCEDURE SalesByCategory
 @CategoryName nvarchar(15),
 @OrdYear int = 1998
AS

SELECT ProductName, SUM(OD.Quantity * (1-OD.Discount) * OD.UnitPrice) AS TotalPurchase
FROM "Order Details" od, Orders o, Products p, Categories c
WHERE od.OrderID = o.OrderID
 AND od.ProductID = p.ProductID
 AND p.CategoryID = c.CategoryID
 AND c.CategoryName = @CategoryName
 AND DATEPART(year,OrderDate) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName

--Call stored procedure
EXEC SalesByCategory 'Seafood', 1997

The procedure has two parameters defined @CategoryName, which is required, and @OrdYear, which has a default value of 1998.

The stored procedure can be called with the EXEC command:

EXEC SalesByCategory 'Seafood', 1997.

The stored procedure shown in the previous demo returns a result set; however, stored procedures can also be used to insert, update, or delete data. Using conditional processing, it is possible to check parameters to make sure the input parameters are valid. It is also possible to run multiple queries and return multiple results with a single stored procedure. That advanced material is beyond the scope of this course. But even the simple stored procedure shown here is valuable as it makes it easy to get a sales report for a specific category of goods without writing a full SELECT query.

Dropping Stored Procedures

Dropping stored procedures is easy enough:

DROP PROCEDURE stored_procedure_name

EXEC spSalesReport2 '11/1/1997','12/1/1997', 'Steve'

Benefits of Stored Procedures

Stored procedures have many benefits, including:

• Speed - Stored procedures are pre-compiled, so the execution plan doesn't have to be figured out each time they're called. This can result in a significant performance improvement.
• Code reuse and abstraction - Stored procedures often involve complex code. It's nice that this code doesn't have to be rewritten over and over again and that even entry-level SQL programmers can make use of it with a simple stored procedure call.
• Security - Permissions can be granted for stored procedures while being restricted for the underlying tables. This allows the DBA to provide a method for SQL programmers and report writers to access and/or manipulate data in a safe way.
• Reduced traffic between client and server - Since the bulk of the query is already stored on the server and only the relatively short stored procedure call has to get sent, traffic to the server is decreased.

All material in this Stored Procedures is copyright 2009 Webucator. The purpose of this website is to help you learn SQL on your own and use of the website implies your agreement to our SQL Tutorial Terms of Service.