Stored Procedures

Understanding Stored Procedures

Stored procedures are simply collections of one or more SQL statements saved for future use. You can think of them as batch files, although in truth they are more than that.

Why to Use Stored Procedures

Stored procedures provides the following benefits :

  • Simplicity
  • Security
  • Performance

In brief, store procedures can be used to :

  • To simplify complex operations (as seen in the previous example) by encapsulating processes into a single easy-to-use unit.

  • To ensure data consistency by not requiring that a series of steps be created over and over. If all developers and applications use the same stored procedure, then the same code will be used by all. An extension of this is to prevent errors. The more steps that need to be performed, the more likely it is that errors will be introduced. Preventing errors ensures data consistency.

  • To simplify change management. If tables, column names, or business logic (or just about anything) changes, then only the stored procedure code needs to be updated, and no one else will need even to be aware that changes were made. An extension of this is security. Restricting access to underlying data via stored procedures reduces the chance of data corruption (unintentional or otherwise).

  • Because stored procedures are usually stored in a compiled form, the DBMS has to do less work to process the command. This results in improved performance.

  • There are SQL language elements and features that are available only within single requests. Stored procedures can use these to write code that is more powerful and flexible.

Executing Stored Procedures

Stored procedures are executed far more often than they are written, so we’ll start there. The SQL statement to execute a stored procedure is simply EXECUTE. EXECUTE takes the name of the stored procedure and any parameters that need to be passed to it.

Here is an example :

exec sp_who2

or using the longer version of the keyword :

EXECUTE sp_who2

Creating Stored Procedures

Lets create a stored procedure that counts the number of customers in a mailing list who have e-mail addresses.

CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;

This stored procedure takes no parameters at all. The calling application retrieves the value by using SQL Server’s return code support. Here a local variable named @cnt is declared using the DECLARE statement (all local variables in SQL Server are named starting with a_ @_). This variable is then used in the SELECT statement so that it contains the value returned by the COUNT() function. Finally, the RETURN statement is used to return the count to the calling application as RETURN @cnt.

To invoke the SQL Server example you could do the following:

DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue;

This code declares a variable to hold whatever the stored procedure returns, executes the store procedure, and then uses a SELECT to display the returned value.

Here’s another example, this time to insert a new order in the Orders table :

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- Declare variable for order number
DECLARE @order_num INTEGER
-- Get current highest order number
SELECT @order_num=MAX(order_num)
FROM Orders
-- Determine next order number
SELECT @order_num=@order_num + 1
-- Insert new order
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(@order_num, GETDATE(), @cust_id)
-- Return order number
RETURN @order_num;

This stored procedure creates a new order in the Orders table. It takes a single parameter, the ID of the customer placing the order. The other two table columns, the order number and order date, are generated automatically within the stored procedure itself. The code first declares a local variable to store the order number. Next, the current highest order number is retrieved (using a MAX() function) and incremented (using a SELECT statement). Then the order is inserted with an INSERT statement using the newly generated order number, the current system date (retrieved using the GETDATE() function), and the passed customer ID. Finally, the order number (which is needed to process order items) is returned as RETURN @order_num.

Here’s a quite different version of the same SQL Server code:

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- Insert new order
INSERT INTO Orders(cust_id)
VALUES(@cust_id)
-- Return order number
SELECT order_num = @@IDENTITY;