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.

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 –

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 ) ENDEXEC 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 –

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 –

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!

1 comment:

Hi,You have specified that we should not use for business logic or for CRUD operations, here i accept that we should not use for business logic, and why not in CRUD operations, if it is not helpful in CRUD operations means, where do we use Stored Procedures.