Sql Stored Procedures

Stored procedures are essentially functions that you can create in the database and reuse. What's neat about them from a usability standpoint is that they can take input parameters and then return a result.

Creating Stored Procedures

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

Syntax

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.

Code Sample: StoredProcedures/Demos/StoredProc.sql

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

Code Explanation

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:

Syntax

DROP PROCEDURE stored_procedure_name