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.
Table of Contents
- 1 Advantages of using stored procedures:
- 2 Creating a simple stored procedure in SQL
- 3 Creating a stored procedure with parameters
- 4 Creating a stored procedure with default parameters values
- 5 Creating a stored procedure with an output parameter
- 6 Creating an encrypted stored procedure in SQL Server
- 7 Creating a temporary procedure in SQL
- 8 Modifying the stored procedure
- 9 Renaming the stored procedure in SQL Server
- 10 Deleting a Stored Procedure
- 11 Handling Exceptions in Stored Procedures
- 12 How many types of stored procedures are there in SQL Server?
- 13 FAQs
Advantages of using stored procedures:
The following are some of the most significant advantages of using stored procedures:
- 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.
- Reusability: Once a stored procedure is created, it can be called from multiple applications. Thus it can reduce development time and increase maintainability.
- Security: Stored procedures can help protect sensitive data from unauthorized access by allowing access only to the procedure instead of the underlying tables.
- Consistency: Stored procedures can ensure that data is consistent by enforcing business rules and data integrity constraints.
- 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.
- Consistent database logic: Stored procedures can enforce business rules and data integrity constraints, ensuring that data is consistent.
- Reduced code complexity: Stored procedures can simplify complex queries by encapsulating them in a single procedure.
- 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
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:
- 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.
- 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
Q: What is a stored procedure in SQL Server?
A stored procedure is a pre-compiled and optimized block of code stored in a SQL Server database. It can be executed repeatedly by client applications.
Stored procedures can accept input parameters, perform complex logic and operations, and return results to the client.
Q: What are the benefits of using a stored procedure in an SQL Server?
Stored procedures can offer several benefits, including improved performance, reduced network traffic, increased security, easier maintenance and troubleshooting, and the ability to reuse code across different applications.
Q: How can you modify an existing stored procedure in SQL Server?
To modify an existing stored procedure, you can use the ALTER PROCEDURE statement, followed by the stored procedure name.
Q: What are some advantages of using stored procedures in SQL Server?
Some advantages of storing procedures include improved performance, better security, easier maintenance and troubleshooting, and the ability to reuse code across different applications.
Stored procedures can also help reduce the amount of data sent over the network. Since only the stored procedure name and parameters need to be passed over the network rather than the entire query.
Additionally, stored procedures can help enforce data consistency and reduce the risk of SQL injection attacks.
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.
- SQL Server Indexing: Clustered vs Non Clustered Index Explained - March 26, 2023
- Mastering Database Normalization: Best Practices and Techniques - March 25, 2023
- CRM Databases: The Key to Enhanced Customer Engagement and Sales - March 23, 2023