SQL Stored Procedures

The purpose of this article to provide understanding and knowledge about SQL Stored Procedure. 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.

Open SQL Server Management Studio (SSMS) and open a New Query window. For this demonstration I will be using the Northwind database so it will help you to understand very easily.

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 –

Add caption

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 'QUEEN'

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 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.

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 tblDepartment

    (

      IntDeptNo INT PRIMARY KEY ,

      Name VARCHAR(20) ,

      Location VARCHAR(20)

    )

CREATE TABLE tblEmployee

    (

      IntEmptID INT PRIMARY KEY ,

      EmpName VARCHAR(20) ,

      Job VARCHAR(20) ,

      MgrNo INT ,

      Sal DECIMAL(8, 2) ,

      IntDeptNo INT REFERENCES tblDepartment ( IntDeptNo )

    )

CREATE TABLE tblUpdatedSalTable

    (

      IntEmptID INT PRIMARY KEY ,

      EmpName VARCHAR(20) ,

      Job VARCHAR(20) ,

      MgrNo INT ,

      Sal DECIMAL(8, 2) ,

      IntDeptNo INT REFERENCES tblDepartment ( IntDeptNo )

    )

INSERT  INTO tblDepartment VALUES  ( 10, 'Sales', 'East' )

INSERT  INTO tblDepartment VALUES  ( 20, 'Cashier', 'West' )

INSERT  INTO tblDepartment VALUES  ( 30, 'Investigation', 'North' )

INSERT  INTO tblDepartment VALUES  ( 40, 'Income', 'South' )

INSERT  INTO tblEmployee VALUES  ( 1008, 'Pushkar', 'Vice President', NULL, 1200, 10 )

INSERT  INTO tblEmployee VALUES  ( 1000, 'Kalpesh', 'Manager', 1008, 3200, 10 )

INSERT  INTO tblEmployee VALUES  ( 1001, 'Vijay', 'Sales Rept', 1000, 2200, 10 )

SELECT  *  FROM    tblDepartment

SELECT  * FROM    tblEmployee

GO

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.IntEmptID  FROM dbo.tblEmployee AS WORKER

            WHERE Sal>  (SELECT Sal FROM dbo.tblEmployee AS MANAGER  WHERE WORKER.MGRNO = MANAGER.IntEmptID)

           

            OPEN complex_cursor ;

            FETCH NEXT FROM complex_cursor INTO @ENO ;

            WHILE ( @@FETCH_STATUS = 0 )

                BEGIN

                    SELECT  @ENO

                    UPDATE  dbo.tblEmployee  SET     JOB = 'MANAGER'  WHERE   IntEmptID = @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

EXEC dbo.UpdateJobOfWorker

About Author:
Pushkar Rathod is a consultant in Systems Plus Pvt. Ltd. Within Systems Plus, he actively contributes to the areas of Technology and Information Security. He can be contacted at pushkar.r@spluspl.com