In this article, we will learn how to create stored procedures in SQL Server with different examples.
SQL Server stored procedure is a batch of statements grouped as a logical unit and stored in the database. The stored procedure accepts the parameters and executes the T-SQL statements in the procedure, returns the result set if any.
To understand differences between functions and stored procedures in SQL Server, you can refer to this article, Functions vs stored procedures in SQL Server and to learn about Partial stored procedures in SQL Server, click Partial stored procedures in SQL Server.
Benefits of using a stored procedure
It can be easily modified: We can easily modify the code inside the stored procedure without the need to restart or deploying the application. For example, If the T-SQL queries are written in the application and if we need to change the logic, we must change the code in the application and re-deploy it. SQL Server Stored procedures eliminate such challenges by storing the code in the database. so, when we want to change the logic inside the procedure we can just do it by simple ALTER PROCEDURE statement.
Reduced network traffic: When we use stored procedures instead of writing T-SQL queries at the application level, only the procedure name is passed over the network instead of the whole T-SQL code.
Reusable: Stored procedures can be executed by multiple users or multiple client applications without the need of writing the code again.
Security: Stored procedures reduce the threat by eliminating direct access to the tables. we can also encrypt the stored procedures while creating them so that source code inside the stored procedure is not visible. Use third-party tools like ApexSQL Decrypt to decrypt the encrypted stored procedures.
Performance: The SQL Server stored procedure when executed for the first time creates a plan and stores it in the buffer pool so that the plan can be reused when it executes next time.
I am creating sample tables that will be used in the examples in this article.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE Product (ProductID INT, ProductName VARCHAR(100) ) GO CREATE TABLE ProductDescription (ProductID INT, ProductDescription VARCHAR(800) ) GO INSERT INTO Product VALUES (680,'HL Road Frame - Black, 58') ,(706,'HL Road Frame - Red, 58') ,(707,'Sport-100 Helmet, Red') GO INSERT INTO ProductDescription VALUES (680,'Replacement mountain wheel for entry-level rider.') ,(706,'Sturdy alloy features a quick-release hub.') ,(707,'Aerodynamic rims for smooth riding.') GO |
Creating a simple stored procedure
We will create a simple stored procedure that joins two tables and returns the result set as shown in the following example.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE PROCEDURE GetProductDesc AS BEGIN SET NOCOUNT ON SELECT P.ProductID,P.ProductName,PD.ProductDescription FROM Product P INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID END |
We can use ‘EXEC ProcedureName’ to execute stored procedures. When we execute the procedure GetProductDesc, the result set looks like below.
Creating a stored procedure with parameters
Let us create a SQL Server stored procedure that accepts the input parameters and processes the records based on the input parameter.
Following is the example of a stored procedure that accepts the parameter.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE PROCEDURE GetProductDesc_withparameters (@PID INT) AS BEGIN SET NOCOUNT ON SELECT P.ProductID,P.ProductName,PD.ProductDescription FROM Product P INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID WHERE P.ProductID=@PID END |
|
1 |
EXEC GetProductDesc_withparameters 706 |
While executing the stored procedure we need to pass the input parameter. Please refer to the below image for the result set.
Creating a stored procedure with default parameters values
Following is the example of a stored procedure with default parameter values.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE PROCEDURE GetProductDesc_withDefaultparameters (@PID INT =706) AS BEGIN SET NOCOUNT ON SELECT P.ProductID,P.ProductName,PD.ProductDescription FROM Product P INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID WHERE P.ProductID=@PID END |
When we execute the above procedure without passing the parameter value, the default value 706 will be used. But when executed passing the value, the default value will be ignored and the passed value will be considered as a parameter.
Creating a stored procedure with an output parameter
Below is the example of a stored procedure with an output parameter. The following example retrieves the EmpID which is an auto identity column when a new employee is inserted.
|
1 |
CREATE TABLE Employee (EmpID int identity(1,1),EmpName varchar(500)) |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE PROCEDURE ins_NewEmp_with_outputparamaters (@Ename varchar(50), @EId int output) AS BEGIN SET NOCOUNT ON INSERT INTO Employee (EmpName) VALUES (@Ename) SELECT @EId= SCOPE_IDENTITY() END |
Executing the stored procedures with output parameters is bit different. We must declare the variable to store the value returned by the output parameter.
|
1 2 3 4 5 |
declare @EmpID INT EXEC ins_NewEmp_with_outputparamaters 'Andrew', @EmpID OUTPUT SELECT @EmpID |
Creating an encrypted stored procedure
We can hide the source code in the stored procedure by creating the procedure with the “ENCRYPTION” option.
Following is the example of an encrypted stored procedure.
|
1 2 3 4 5 6 7 8 |
CREATE PROCEDURE GetEmployees WITH ENCRYPTION AS BEGIN SET NOCOUNT ON SELECT EmpID,EmpName from Employee END |
When we try to view the code of the SQL Server stored procedure using sp_helptext, it returns “The text for object ‘GetEmployees’ is encrypted.”
When you try to script the encrypted stored procedure from SQL Server management studio, it throws an error as below.
Creating a temporary procedure
Like the temporary table, we can create temporary procedures as well. There are two types of temporary procedures, one is a local temporary stored procedure and another one is a global temporary procedure.
These procedures are created in the tempdb database.
Local temporary SQL Server stored procedures: These are created with # as prefix and can be accessed only in the session where it created. This procedure is automatically dropped when the connection is closed.
Following is the example of creating a local temporary procedure.
|
1 2 3 4 5 |
CREATE PROCEDURE #Temp AS BEGIN PRINT 'Local temp procedure' END |
Global temporary SQL Server stored procedure: These procedures are created with ## as prefix and can be accessed on the other sessions as well. This procedure is automatically dropped when the connection which is used to create the procedure is closed.
Below is the example of creating a global temporary procedure.
|
1 2 3 4 5 |
CREATE PROCEDURE ##TEMP AS BEGIN PRINT 'Global temp procedure' END |
Modifying the stored procedure
Use the ALTER PROCEDURE statement to modify the existing stored procedure. Following is the example of modifying the existing procedure.
|
1 2 3 4 5 6 7 8 9 10 |
ALTER PROCEDURE GetProductDesc AS BEGIN SET NOCOUNT ON SELECT P.ProductID,P.ProductName,PD.ProductDescription FROM Product P INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID END |
Renaming the stored procedure
To rename a stored procedure using T-SQL, use system stored procedure sp_rename. Following is the example that renames the procedure “GetProductDesc” to a new name “GetProductDesc_new”.
|
1 |
sp_rename 'GetProductDesc','GetProductDesc_new' |
Conclusion
In this article, we explored SQL Server stored procedures with different examples. In case you have any questions, please feel free to ask in the comment section below.
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019