SQL Server: 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 

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