Ranga Babu
SQL Server stored procedures with default parameters

SQL Server stored procedures for beginners

July 29, 2019 by

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.

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.

We can use ‘EXEC ProcedureName’ to execute stored procedures. When we execute the procedure GetProductDesc, the result set looks like below.

result set of a SQL Server stored procedure

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.

While executing the stored procedure we need to pass the input parameter. Please refer to the below image for the result set.

SQL Server stored procedure with parameters

Creating a stored procedure with default parameters values

Following is the example of a stored procedure with default parameter values.

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.

SQL Server stored procedures with default parameters

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.

Executing the stored procedures with output parameters is bit different. We must declare the variable to store the value returned by the output parameter.

SQL Server stored procedures with default parameters


inserted records in the table

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.

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.”

encrypted SQL Server stored procedures

When you try to script the encrypted stored procedure from SQL Server management studio, it throws an error as below.

encrypted SQL Server stored procedures

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.

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.

Modifying the stored procedure

Use the ALTER PROCEDURE statement to modify the existing stored procedure. Following is the example of modifying the existing procedure.

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”.

renaming a SQL Server stored procedure

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.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
18,411 Views