SQL Server Stored Procedures - Back to Basics

In this article, we will see how to create Stored Procedures in SQL Server. We will also explore Stored Procedures with INPUT/OUTPUT Parameters and Stored Procedures with Transactions and Cursors.
Stored Procedure is a group of T-SQL statements compiled into a single execution plan. It offers various functionalities like –

• Modularity – Stored Procedures in SQL Server offers Modularity which allows us to divide the program/business logic into number of groups which can be recombined and reused as per our requirements.
• Easy Maintenance – The required business logic and rules can be enforced and can be changed at any point of time as Stored Procedures are single point of control.
• Reusability – Once you write a stored procedure, you can reuse the same over and over again in any application.
• Improved Performance – The Stored Procedures are the compiled T-SQL blocks.

Likewise, there are number of benefits which we can achieve at database level by writing the stored procedures in SQL Server. Stored Procedures can be written with or without parameters to change the output and execute the business logic based on the conditional statements.

There are some limitations to stored procedures too, which I have briefly outlined at the end of this article.
Let’s try writing some stored procedures and explore their features. Open SQL Server Management Studio (SSMS) and open a New Query window. For this demonstration I will be using the Northwind database.
Here are the tables which we will make use for querying the data in our stored procedures –

USE Northwind
GO
--Tables to be used during Stored Procedures
SELECT * FROM Customers
SELECT * FROM Employees
SELECT * FROM Orders
SELECT * FROM [Order Details]
SELECT * FROM Products
GO

A Simple Stored Procedure

We will start by creating a stored procedure which will fetch all the order details with product name and supplier details. Let’s write this code in our SSMS Query window –

CREATE PROCEDURE FetchAllOrderDetails
AS
BEGIN
    SELECT O.OrderID,MONTH(O.OrderDate) Order_Month,
    P.ProductName,P.UnitPrice,P.UnitsInStock,
    S.CompanyName FROM Orders O
    INNER JOIN [Order Details] OD
    ON O.OrderID=OD.OrderID
    INNER JOIN Products P
    ON OD.ProductID=P.ProductID
    INNER JOIN Suppliers S
    ON P.SupplierID=S.SupplierID
END

EXEC FetchAllOrderDetails

The output of the above stored procedure is as follows –

Stored Procedure with a Parameter

Now we will write another stored procedure to fetch the product details and category details of the products purchased by the customer. We will input a customer ID to our stored procedure.

CREATE PROCEDURE CustomerProductDetails
(
    @p_CustomerID NVARCHAR(10)
)
AS
BEGIN
    SELECT CAT.CategoryName,CAT.[Description],
    P.ProductName,P.UnitPrice,P.UnitsInStock
    FROM Customers C INNER JOIN Orders O
    ON C.CustomerID=O.CustomerID
    INNER JOIN [Order Details] OD
    ON O.OrderID=OD.OrderID
    INNER JOIN Products P
    ON OD.ProductID=P.ProductID
    INNER JOIN Categories CAT
    ON P.CategoryID=CAT.CategoryID
    WHERE C.CustomerID=@p_CustomerID
END

EXEC CustomerProductDetails 'ALFKI'

The output of above stored procedure is as shown below –

Stored Procedure with Two Parameters

Let’s write one more stored procedure with two input parameters which will fetch the details of an employee(s) who has/have processed the maximum orders in a given month and year. The code is shown below –

CREATE PROCEDURE EmployeeOfTheMonth
(
    @p_Year INT,
    @p_Month NVARCHAR(10)
)
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID IN
    (
        SELECT EmployeeID FROM
        (
            SELECT top 1 EmployeeID, COUNT(OrderID) TotalOrders
            FROM Orders
            WHERE YEAR(OrderDate)=@p_Year
            AND DATENAME(MONTH,OrderDate)=@p_Month
            GROUP BY EmployeeID
            ORDER BY TotalOrders DESC
        ) AS EmployeeOfTheMonth
    )
END

EXEC EmployeeOfTheMonth 1997,'June'

The output of above stored procedure is as shown below –

Stored Procedure with INPUT and OUTPUT parameter

We will vary the stored procedure we just wrote, this time with an INPUT and OUTPUT parameters. We will try fetching the product details which are supplied by a given supplier ID and will return the supplier’s Contact Name and Company Name. Let’s write the below code in our query pad –

CREATE PROCEDURE FetchSupplierProducts
(
    @p_SupplierID INT,
    @p_SupplierName NVARCHAR(30) OUTPUT,
    @p_CompanyName NVARCHAR(30) OUTPUT
)
AS
BEGIN
    SELECT P.ProductID,P.ProductName,P.UnitPrice FROM Products P INNER JOIN Suppliers S
    ON P.SupplierID=S.SupplierID WHERE S.SupplierID=@p_SupplierID
    SELECT @p_SupplierName=ContactName,@p_CompanyName=CompanyName FROM Suppliers
    WHERE SupplierID=@p_SupplierID
END

To test the stored procedure, write the following code –

DECLARE @v_ContactName NVARCHAR(30)
DECLARE @v_CompanyName NVARCHAR(30)
EXEC FetchSupplierProducts 1,@v_ContactName OUTPUT,@v_CompanyName OUTPUT
SELECT @v_CompanyName CompanyName,@v_ContactName SupplierName

The output of the above stored procedure is as shown below –

Stored Procedure with Transactions and Try-Catch Block

For the next demonstration, we will create three tables and add some dummy data in the same. Write below following code to create the three tables and insert some data –

CREATE TABLE Dept
(
    DEPTNO INT PRIMARY KEY,
    DNAME VARCHAR(20),
    LOC VARCHAR(20)
) 

CREATE TABLE Emp
(
    EMPID INT PRIMARY KEY,
    ENAME VARCHAR(20),
    JOB VARCHAR(20),
    MGRNO INT,
    SAL DECIMAL(8,2),
    DEPTNO INT REFERENCES DEPT(DEPTNO)
) 

CREATE TABLE UpdatedSalTable
(
    EMPID INT PRIMARY KEY,
    ENAME VARCHAR(20),
    JOB VARCHAR(20),
    MGRNO INT,
    SAL DECIMAL(8,2),
    DEPTNO INT REFERENCES DEPT(DEPTNO)
) 

INSERT INTO Dept VALUES(10,'SALES','NORTH')
INSERT INTO Dept VALUES(20,'ACCOUNTS','SOUTH')
INSERT INTO Dept VALUES(30,'PRODUCTION','WEST')
INSERT INTO Dept VALUES(40,'TRAVEL','EAST')
INSERT INTO Emp VALUES(1008,'IIII','VP',NULL,1200,10)
INSERT INTO Emp VALUES(1000,'AAAA','MANAGER',1008,3200,10)
INSERT INTO Emp VALUES(1001,'BBBB','Sales Rept',1000,2200,10)
INSERT INTO Emp VALUES(1002,'CCCC','Account Mgr',1008,4200,20)
INSERT INTO Emp VALUES(1003,'DDDD','Analyst',1002,5000,20)
INSERT INTO Emp VALUES(1004,'EEEE','Analyst',1002,5000,20)
INSERT INTO Emp VALUES(1005,'FFFF','Field Manager',1008,7200,30)
INSERT INTO Emp VALUES(1006,'GGGG','Prod Eng',1005,3200,30)
INSERT INTO Emp VALUES(1007,'HHHH','Site Eng',1005,4200,30)
SELECT * FROM Dept
SELECT * FROM Emp
GO

Now create a stored procedure which will implement the transaction with error handling using TRY-CATCH block. The stored procedure will update the salary of an employee if the location of the employee’s department is ‘SOUTH’ and commit the transaction. It will also store the updated employee’s record into a separate table. Let’s write the following code in our query pad –

CREATE PROCEDURE UpdateEmployeeSalary
(
    @p_EmployeeID INT
)
AS
BEGIN
    DECLARE @v_Location NVARCHAR(10)
    DECLARE @v_DeptID INT
    DECLARE @UpdateSal NVARCHAR(20)='Salary Update Transaction'
    SELECT @v_DeptID = DEPTNO FROM Emp WHERE EMPID=@p_EmployeeID
    SELECT @v_Location=LOC FROM Dept WHERE DEPTNO=@v_DeptID
    BEGIN TRY
        BEGIN TRAN @UpdateSal
            IF(UPPER(@v_Location)='SOUTH')
                BEGIN
                    UPDATE Emp SET SAL=SAL+1000 WHERE EMPID=@p_EmployeeID
                    INSERT UpdatedSalTable
                    SELECT * FROM EMP WHERE EMPID=@p_EmployeeID                   
                END
            ELSE
                BEGIN
                    PRINT 'NO UPDATES'
                END
        COMMIT TRAN @UpdateSal
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_SEVERITY()
        ROLLBACK TRAN @UpdateSal
    END CATCH
END

Let’s test our stored procedure by writing the following code –

EXEC UpdateEmployeeSalary 1002
SELECT * FROM UpdatedSalTable

The output of updated employee is as shown below –

Stored Procedure using a Cursor

The next stored procedure we will write will make use of CURSOR to modify the number of rows one by one. The stored procedure fetches each employee one by one and checks if the salary of an employee is greater than the manager’s salary. If the salary is greater than the manager’s salary, the job of an employee will be updated to Manager. Wish that happened in real life too eh! Let’s write the following code in our query pad –

CREATE PROCEDURE UpdateJobOfWorker
AS
BEGIN
DECLARE @UpdateSal NVARCHAR(20)='Salary Update Transaction'
BEGIN TRY
        BEGIN TRAN @UpdateSal
            DECLARE @ENO INT
            DECLARE complex_cursor CURSOR FOR
                SELECT WORKER.EMPID
                FROM dbo.EMP AS WORKER
                WHERE SAL>
                     (SELECT SAL
                      FROM dbo.EMP AS MANAGER
                      WHERE WORKER.MGRNO = MANAGER.EMPID)
            OPEN complex_cursor;
            FETCH NEXT FROM complex_cursor INTO @ENO;
            WHILE (@@FETCH_STATUS=0)
                BEGIN
                    SELECT @ENO
                    UPDATE dbo.EMP
                    SET JOB = 'MANAGER'
                    WHERE EMPID=@ENO;
                    FETCH NEXT FROM complex_cursor INTO @ENO;
                END
            CLOSE complex_cursor;
            DEALLOCATE complex_cursor;
        COMMIT TRAN @UpdateSal
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE(),ERROR_NUMBER(),ERROR_SEVERITY()
        ROLLBACK TRAN @UpdateSal
        CLOSE complex_cursor;
        DEALLOCATE complex_cursor;
    END CATCH
END

Test the stored procedure  –

EXEC UpdateJobOfWorker

Some Stored Procedure Limitations

Two limitations that come to my mind that using too many Stored Procedures can put some load on the server as it increases the amount of processing that the server has to do. The other one is that SQL Server stored procedures do not follow the ANSI-99 standards, so porting them to a different database requires some rework.

A general rule of thumb to following while using stored procedures is that don't use them to implement business logic or for performing CRUD operations.

And that’s it. In this article, we saw the benefits of writing the stored procedures with various examples like Stored Procedures without and with INPUT/OUTPUT parameters, Stored Procedure with transactions and stored procedures with CURSORS. Hope you enjoyed reading the article!