Stored Procedure in SQL Server – A Complete Guide [with Examples]

If you’re working with SQL Server, you may have come across the concept of stored procedures. A stored procedure is a precompiled set of SQL statements that we can save in the database for later use. Once it is created, we can execute it multiple times without recompiling the code. 

This article aims to cover everything you need to know about stored procedures in SQL Server, including benefits, creation, modification, and exception handling.

Before diving into how to create a stored procedure in SQL Server, let’s discuss the advantages of using them.

Advantages of using stored procedures:

The following are some of the most significant advantages of using stored procedures:

1. Improved Performance: Stored procedures are precompiled, which means they execute faster than ad-hoc SQL statements. Additionally, they are cached in memory, reducing the time needed to compile and execute them.
2. Reusability: Once a stored procedure is created, it can be called from multiple applications. Thus it can reduce development time and increase maintainability.
3. Security: Stored procedures can help protect sensitive data from unauthorized access by allowing access only to the procedure instead of the underlying tables.
4. Consistency: Stored procedures can ensure that data is consistent by enforcing business rules and data integrity constraints.
5. Reduced network traffic: Since the server only needs to pass the stored procedure name instead of the entire query, this helps minimize network traffic and optimize performance.
6. Consistent database logic: Stored procedures can enforce business rules and data integrity constraints, ensuring that data is consistent.
7. Reduced code complexity: Stored procedures can simplify complex queries by encapsulating them in a single procedure.
8. Cached: When a stored procedure is executed for the first time, a plan is created and cached in the buffer pool. It will speed up subsequent executions and can lead to significant performance gains.

A sample Employees table is created to be used in the examples throughout this article.

CREATE TABLE [dbo].[Employees](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[City] [nvarchar](50) NOT NULL,
	[State] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([Id] ASC)
)
GO

INSERT INTO Employees (Name, City, State) 
VALUES ('Shekh Ali', 'Delhi', 'Delhi'),
       ('Robert', 'Los Angeles', 'CA'),
       ('Denni', 'Chicago', 'IL'),
       ('David', 'Houston', 'TX'),
       ('Emily', 'Miami', 'FL');

Creating a simple stored procedure in SQL

Now that we’ve covered the benefits of stored procedures let’s move on to creating a simple stored procedure in SQL Server.

Example: Create Stored Procedure

The following is an example of a stored procedure that returns all the rows from the Employees table.

CREATE PROCEDURE GetAllRows
AS
BEGIN
SELECT * FROM Employees
END

The stored procedures can be executed using the syntax “EXEC ProcedureName”. The resulting set appears as follows after executing the “GetAllRows” procedure.

-- Executing stored procedure
EXECUTE GetAllRows;

Creating a stored procedure with parameters

Stored procedures can take parameters, making them more flexible and dynamic. Here is an example of a stored procedure that accepts a parameter:

CREATE PROCEDURE GetRowsByCity
@City nvarchar(50)
AS
BEGIN
SELECT * FROM Employees WHERE City = @City
END

In this example, the stored procedure takes a single parameter called @City, which is used in the WHERE clause to filter results based on a specific city. To execute this stored procedure, pass the value of the parameter:

EXEC GetRowsByCity 'Delhi'

Creating a stored procedure with default parameters values

Stored procedures can also have default parameter values. In the following example, the @City parameter has a default value of ‘Chicago’:

Let’s alter the existing stored procedure to set the default parameter value.

ALTER PROCEDURE GetRowsByCity
@City nvarchar(50) = 'Chicago'
AS
BEGIN
SELECT * FROM Employees WHERE City = @City
END

In this case, if a value is not passed for @City, the stored procedure will use the default value of ‘Chicago’.

Creating a stored procedure with an output parameter

Stored procedures can also have output parameters that allow them to return a value to the calling program. Here is an example of a stored procedure that returns the number of rows in a table:

CREATE PROCEDURE GetRowCount
@Count INT OUTPUT
AS
BEGIN
SELECT @Count = COUNT(*) FROM Employees
END

In this example, the @Count parameter is an output parameter that returns the count of rows in the Employees table. To execute this stored procedure, pass a variable as the output parameter:

DECLARE @RowCount INT
EXEC GetRowCount @Count = @RowCount OUTPUT
PRINT @RowCount
-- Output: 5

Creating an encrypted stored procedure in SQL Server

Creating an encrypted stored procedure is one way to safeguard its contents. Below is an example of how to create an encrypted stored procedure:

CREATE PROCEDURE EncryptProc WITH ENCRYPTION
AS
BEGIN
SELECT * FROM Employees
END

In this example, the WITH ENCRYPTION option encrypts the stored procedure code, preventing users from viewing it in clear text without the appropriate permissions.
For example, You will not allow seeing the content of the stored procedure by using the SP_HELPTEXT PocedureName.

Creating a temporary procedure in SQL

Temporary stored procedures are created and used within a specific session or connection. They are useful for one-time or temporary tasks. Here’s an example of how to create a temporary stored procedure:

CREATE PROCEDURE #TempProc
AS
BEGIN
SELECT * FROM Employees
END

In this example, the # symbol indicates that this is a temporary stored procedure. It will only exist for the duration of the current session or connection.

Modifying the stored procedure

To modify an existing stored procedure, use the ALTER PROCEDURE statement. Here is an example of how to add a new parameter to an existing stored procedure:

ALTER PROCEDURE GetRowsByCity
@City nvarchar(50),
@State nvarchar(50)
AS
BEGIN
SELECT * FROM Employees WHERE City = @City AND State = @State
END

In this example, we added a new parameter @State to the GetRowsByCity stored procedure. Existing calls to the stored procedure will still work, but new calls can take advantage of the added functionality.

Renaming the stored procedure in SQL Server

To rename an existing stored procedure, use the sp_rename system stored procedure. Here is an example of how to rename a stored procedure:

-- Renaming existing stored procedure
EXEC sp_rename 'GetAllRows', 'GetAllData'

In this example, we renamed the GetAllRows stored procedure to GetAllData.

Deleting a Stored Procedure

To delete an existing stored procedure, use the DROP PROCEDURE statement. Here is an example of how to delete a stored procedure:

DROP PROCEDURE GetRowsByCity

In this example, we deleted the GetRowsByCity stored procedure.

Handling Exceptions in Stored Procedures

It’s essential to handle exceptions in stored procedures to prevent errors from affecting your application or database. The TRY CATCH construct is used to handle exceptions in stored procedures. Here’s an example of how to use the TRY CATCH block:

CREATE PROCEDURE DivideByZero
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Dividend INT = 10;
DECLARE @Divisor INT = 0;

BEGIN TRY
-- generates an error by dividing by zero
SELECT @Dividend / @Divisor;
END TRY

BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage,
       ERROR_NUMBER() AS ErrorNumber,
       ERROR_STATE() AS ErrorState,
       ERROR_SEVERITY() AS ErrorSeverity,
       ERROR_LINE() AS ErrorLine,
       ERROR_PROCEDURE() AS ErrorProcedure;  
END CATCH
END

In this example, we attempted to divide the @Dividend by @Divisor, resulting in a divide by zero error. The TRY…CATCH block catches the error and returns the error message instead of letting it crash the stored procedure.

How many types of stored procedures are there in SQL Server?

In SQL Server, there are mainly two types of stored procedures:

1. System-defined stored procedures: These are the pre-defined stored procedures installed with SQL Server. Microsoft created these procedures and used them for specific tasks, such as managing security or creating a backup.
2. User-defined stored procedures: These are the stored procedures that you create yourself. User-defined stored procedures are created and stored in a user-defined database. We can use these stored procedures to encapsulate business logic or complex database operations. User-defined stored procedures can also be shared with other users, making them a powerful tool for collaboration and data sharing.

Reference: MSDN-Stored Procedure In SQL

FAQs

You might want to read this too:

• Function vs Stored procedure in SQL Server
• Having VS Where Clause in SQL
• Types of stored procedure in sql server
• SQL Server TRY_CAST() Function with [Examples]
• Delete vs Truncate vs Drop in SQL Server
• UNION vs UNION ALL in SQL SERVER
• View In SQL | Types Of Views In SQL Server
• Triggers in SQL Server
• Different ways to delete duplicate rows in SQL Server
• Types of Joins in SQL Server
• SQL pivot tables: Understanding pivot tables in SQL Server
• SQL Server Try Catch: Error Handling in SQL Server With [Examples]
• Group by Month in SQL: A Comprehensive Guide with Examples in SQL Server

Let others know about this post by sharing it and leaving your thoughts in the comments section.

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024
• How to Reverse an Array in C# with Examples - February 12, 2024