Post
Ask Question

Stored Procedures In SQL Server

Introduction

A stored procedure in SQL is a set of SQL statements or a query that is written, stored, and executed in SQL Server. The stored procedure query is precompiled and cached on the server side, and when an application or API or applications call the stored procedure, it is quickly available and executed. This article is for beginners who want to learn everything about stored procedures in SQL Server. 

In this tutorial, we will cover the following topics.

  1. What is a Stored Procedure?
  2. When to use a Stored Procedure?
  3. How to create a Stored Procedure?
  4. How do you create a Stored Procedure with parameters?
  5. How to modify a Stored Procedure?
  6. How do you view the text of the Stored Procedure that we created?
  7. How to drop a Stored Procedure?
  8. How do you create and execute Stored Procedures with an output parameter?
  9. Do stored Procedures return values?
  10. Difference between Stored Procedures with output parameters and return values
  11. Stored Procedure with an optional parameter
  12. Advantages of using a Stored Procedure in SQL Server
  13. Stored Procedures vs. Inline SQL

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So, if you have an SQL query that you write over and over again, save it as a stored procedure and then just call it to execute it. Stored procedures are used to speed up database processing because they run on the server side instead of the client side (that's why they're called server-side). This means that when you call that stored procedure from your application, all of its execution is done on the server side instead of having to send all those queries from your client directly to your database every time. So, each time someone calls this procedure from their application or whenever this procedure runs itself, it gets executed directly in the database engine (which performs much faster than executing queries through clients). Another benefit of using stored procedures is centralizing logic. It is much easier to maintain a stored procedure than inline SQL. If you need to change the logic, all you need to do is change the backend query. 

When should you use a Stored Procedure in SQL Server?

If there is a situation where you need to write and execute the same query again and again, then you can create a Stored Procedure since they are very easy to maintain and reuse. Stored procedures are also secure and much faster to execute because they run on the server side. 

If your database is being accessed by multiple applications or APIs, and they all share the same SQL statements, it's much easier to create them as stored procedures and call them without having duplicate SQL statements in multiple applications.

If your backend database and its objects change often, it's better to use stored procedures because then you don't need to update and redeploy your applications. All changes are done on the backend only. 

How do you create a Stored Procedure in SQL Server?

In SQL Server, let's create two new tables using the steps below.

Step 1. Copy the following SQL query to create a new table named tblEmployees.

CREATE TABLE tblemployees (
    EmployeeId INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    Gender VARCHAR(255) NOT NULL,
    City VARCHAR(255) NOT NULL,
    EmployeeDepartmentId INT NULL
);

After executing the above query, a new table tblEmployees, is created in the database.

Step 2. Now insert a few rows into the table (tblEmployees) by executing the below SQL query.

INSERT INTO tblemployees (EmployeeId, Name, Gender, City, EmployeeDepartmentId)
VALUES (1, 'Tvor', 'Female', 'Los', 3),
       (2, 'Lara', 'Female', 'Tomb Raider', 3),
       (3, 'Sam Fisher', 'Male', 'SplinterCell', 3),
       (5, 'Aiden Pearce', 'Male', 'Chicago', 1),
       (6, 'T-bone', 'Male', 'Chicago', 1),
       (7, 'Kratos', 'Male', 'God Of War', 2),
       (8, 'Michael', 'Male', 'GTA-V', 2),
       (9, 'Captain Price', 'Male', 'Call Of Duty', 2),
       (10, 'New record', 'Male', 'Form collection', 2),
       (11, 'Here is the new record', 'Female', 'WC', 2),
       (12, 'Sample', 'Male', 'test', 2),
       (13, 'James Cameron', 'Male', 'Califomia', 1),
       (22, 'Harmeet', 'Female', 'Kolkata', 1),
       (30, 'Harry', 'Male', 'Hogwarts', 1),
       (1032, 'ABC', 'Male', 'XYZ', NULL);

After executing the above query, you can see multiple records is inserted into the table (tblEmployees)

Step 3. Copy the following SQL query to create a new table named tblDepartments.

CREATE TABLE tblDepartments (
    DepartmentId INT NOT NULL,
    DepartmentName VARCHAR(255) NOT NULL
);

After executing the above query, a new table tblDepartments, is created in the database.

Step 4. Now insert a few rows into the table (tblDepartments) by executing the below SQL query.

INSERT INTO tblDepartments (DepartmentId, DepartmentName)
VALUES (1, 'IT'),
       (2, 'HR'),
       (3, 'Payroll'),
       (5, 'Other Department');

After executing the above query, you can see multiple records is inserted into the table (tblDepartments).

Step 5: Copy the following SQL query to see the records of both tables.

SELECT * FROM tblEmployees;
SELECT * FROM tblDepartments;

After executing the above query, records of both tables are displayed in the results section below the query editor as shown below figure.

 

Now, let's say our business requirements are such that we want to display the columns EmployeeId, Name, Gender, and DepartmentName of all the employees from these two tables using an INNER-JOIN.

SELECT EmployeeId,Name,Gender,DepartmentName  
FROM tblEmployees  
INNER JOIN tblDepartments  
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId  

Execute it to see the results.

 

To get the preceding result, we need to write a lot of code. What if there is a situation where we need to execute the same query repeatedly? For that we need to write the preceding query every single time and in a real scenario, the query could be of 100 or 200 lines. So, in that case, we can use a Stored Procedure.

To create a Stored Procedure, we need to use the following syntax.

CREATE PROCEDURE [procedure_name]
(
    [parameter1] [type],
    [parameter2] [type]
)
AS
BEGIN
    -- SQL statements go here
END

Now let's add the INNER JOIN query inside the Begin and End block of the Stored Procedure.

CREATE PROCEDURE spEmployee  
AS  
BEGIN  
SELECT EmployeeId,Name,Gender,DepartmentName  
FROM tblEmployees  
INNER JOIN tblDepartments  
ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId  
END 

 Execute the preceding query.

If you want to see where this Stored Procedure is stored, then use the following procedure.

  • Expand databases
  • Expand the database where your table is present
  • Expand Programmability
  • Expand Stored Procedure

You will see the Stored Procedure spEmployee as in the following.

 

Let's execute the Stored Procedure.

To execute a Stored Procedure, write the following.

EXECUTE spEmployee;

 

How to create a Stored Procedure with parameters?

In SQL Server, the @ symbol is prefixed with the name of the parameters and variables.

CREATE PROCEDURE spEmployeeByGender  
@Gender NVARCHAR(10)  
AS  
BEGIN  
SELECT EmployeeId,Name,Gender,DepartmentName  
FROM tblEmployees  
INNER JOIN tblDepartments  
ON tblEmployees.EmployeeDepartmentId =  tblDepartments.DepartmentId  
WHERE Gender = @Gender  
END  

Just after the name of the Stored Procedure, we can declare the parameters @parameterName type.

In the preceding Stored Procedure, spEmployeeByGender, we have added a parameter, and before the end block, we added a filter using the Where clause. So, whatever value we pass for @Gender will be assigned in the Where clause and that value will become the value for the Gender column.

EXECUTE spEmployeeByGender 'Female';

 

How to modify a Stored Procedure in SQL Server?

To modify a table, we use ALTER TABLE table_name, and to create a Stored Procedure, we can use ALTER PROCEDURE stored_Procedure_Name.

ALTER PROCEDURE spEmployeeByGender  
@Department NVARCHAR(50),  
@Gender NVARCHAR(10)  
AS  
BEGIN  
SELECT EmployeeId,Name,Gender,DepartmentName  
FROM tblEmployees  
INNER JOIN tblDepartments  
ON tblEmployees.EmployeeDepartmentId =tblDepartments.DepartmentId  
WHERE Gender = @Gender  
AND DepartmentName = @Department  
END  

In the preceding query, we have added another input parameter @Department and then @Gender, separated by a comma.

In the Where clause now, we have added two filter conditions.

Execute the preceding query.

Pass the DepartmentName in the first parameter and Gender in the second parameter.

EXECUTE spEmployeeByGender 'IT','Male';  

 

Now we have only the IT department and Male records.

What will happen if we don't specify a parameter value for @Gender?

EXECUTE spEmployeeByGender 'IT';  

We will get an error because both of the parameters are necessary.

 

How to view the text of the Stored Procedure

Now let's say, for some reason, we lost the query we wrote to create this spEmployeeByGender Stored Procedure, and we want to retrieve it by any means possible.

To get the query we wrote to create this Stored Procedure, we can execute a system Stored Procedure sp_helptext.

EXECUTE sp_helptext 'spEmployeeByGender';  

Execute the preceding query, and in the output, you will see the query we wrote to create the Stored Procedure.

 

How to drop a Stored Procedure in SQL Server?

To update a Stored Procedure, we use the ALTER statement, and to drop a Stored Procedure, we use the DROP statement in SQL Server. The following statement deletes spEmployeeByGender stored proc. 

DROP PROCEDURE spEmployeeByGender;  

How to create and execute a Stored Procedure with an output parameter?

To create a Stored Procedure with an output parameter, we use the out or output keyword.

In the tblEmployee table, I have 15 records. Let's say we want to count the total number of employees whose EmployeeDepartmentId is 1.

 

Create a Stored Procedure with an output parameter as in the following:

--output parameter  
CREATE PROC spEmployeeByDepartment  
@DepartmentId INT,  
@TotalEmployees INT OUT  
AS  
BEGIN  
SELECT @TotalEmployees = COUNT(EmployeeId)  
FROM tblEmployees  
WHERE EmployeeDepartmentId = @DepartmentId  
END  

Look at the previous stored procedure that we created. There are two parameters: one is @DepartmentId, which is an input parameter, and another is the @TotalEmployees parameter, which is an output parameter.

The query we wrote inside the Begin and End block is very straightforward. All we are doing is we are filtering the records using a WHERE clause. So, whatever value we pass for the int @DepartmentId parameter will be assigned as a value for the EmployeeDepartmentId column, and based on this filter, the total employees present in that specific department will be retrieved.

So, we have created our Stored Procedure. Now let's see how to execute it.

The Output parameter returns a value back, and to hold that value, we need to create a variable.

To create a new variable, we use a DECLARE keyword.

DECLARE @total INT;
EXEC spEmployeeByDepartment @DepartmentId = 1, @TotalEmployees = @total OUTPUT;
SELECT @total;

Pass the declared variable in as the second parameter, and be sure to specify the OUT or OUTPUT keyword, or the @Output will be null.

Execute the preceding query altogether.

 

So, there are five employees whose department ID is 1.

Look at the output. It is correct, but what if we want to provide a column named “TotalEployees” to make it more readable?

For that, all we need to do is use AS give_name.

DECLARE @Output INT  
Execute spEmployeeByDepartment 1,@Output OUT  
SELECT @Output AS TotalEmployees  

 

Stored Procedure return values

When we create and execute a Stored Procedure, we get an integer status value back. It might be zero or one. If it returns zero, then that means success. If it returns one, then that means failure.

Let's look at an example of it.

Go to the solution and expand the database you are using.

Right-click on spEmployeeByDepartment and select Execute Stored Procedure.

 

An Execute Stored Procedure window will pop up.

For the @DepartmentId, pass the value as 1, which will return TotalEmployees.

 

Click OK.

Once we click OK, some auto-generated code will be added to a new query window, and we will get the total number of employees present in departmentId 1.

But with that, you will see another output that is a return value, and here, the return value is 0. That means the Stored Procedure is executed successfully.

DECLARE @return_value int,
@TotalEmployees int
EXEC @return_value = [dbo].[spEmployeeByDepartment]
@Departmentid= 1,
@TotalEmployees = @TotalEmployees OUTPUT
SELECT @TotalEmployees as N'@TotalEmployees'
SELECT 'Return Value' = @return_value
GO 

To understand it, let's see what the differences are between Stored Procedures with output parameters and return values.

Difference between Stored Procedure with output parameters and return values

To differentiate between the output parameter and return value, let's create a Stored Procedure.

  • Create a Store Procedure - 1 (Name-spOutputById) 
    CREATE PROC `
@Id INT,  
@Name NVARCHAR(50) OUTPUT,  
@Gender NVARCHAR(10) OUTPUT  
AS  
BEGIN  
SELECT @Name = Name, @Gender = Gender  
from tblEmployees WHERE EmployeeId = @Id  
END;  
  • Create another Store Procedure - 2 (Name-spReturnById) 
    CREATE PROC spReturnById
@Id INT
AS
BEGIN
   RETURN ( SELECT Name FROM tblEmployees WHERE EmployeeId = @Id)
END

Create two Stored Procedures using the above-mentioned queries. Create one Stored Procedure with output parameters and another with return values.

In Stored Procedure spOutputById, there are three parameters. The first parameter is the input parameter and the rest of the two parameters are the output parameters.

Between the BEGIN and END blocks, we wrote a SELECT statement where we are assigning the Name and Gender of the Employees to the output parameters whose Id matches with the Id value we pass.

In the Stored Procedure spReturnById, to return a value, we use the RETURN keyword. Inside this RETURN clause, we wrote a query that will return the Name of the employee whose Id matches the Id value we pass.

Create both of the Stored Procedures one by one.

The next step is to execute these Stored Procedures.

--executing Output Stored Procedure  
DECLARE @EmployeeName NVARCHAR(50)  
DECLARE @EmployeeGender NVARCHAR(10)  
EXEC spOutputById 1,@EmployeeName OUT,@EmployeeGender OUT  
SELECT @EmployeeName AS EmployeeName, @EmployeeGender AS EmployeeGender  

Declare two variables of type nvarchar. Pass the id value in the first parameter of the spOutputById Stored Procedure. In the second parameter, pass @EmployeeName. In the third parameter, pass @EmployeeGender, and since these two parameters are output parameters, it is necessary to pass an OUT or OUTPUT keyword after the variable names.

Now, all we need to do is select these records.

Execute it.

 

So, we got the records whose ID is 1.

Now, let's see how to do the same thing using a return value.

EXECUTE spReturnById 1;  

Execute it.

 

We got an error.

The second difference

The reason for this error is that the return clause is trying to return a nvarchar value back, but the return clause can only return a single integer value back.

In short, the output parameters can return more than one value, and these can be of any datatype, whereas a return value can return a single value of type integer.

Stored Procedure with an optional parameter

In the previous topics, we discussed how to create Stored Procedures with and without input parameters. We have also seen how to create a Stored Procedure with output parameters, and we have also discussed the difference between a Stored Procedure with output parameters and return values.

Now, let's see how to create a Stored Procedure using optional parameters.

In C#, to create a method with an optional parameter, we specify default values to the parameters, and just like in SQL Server, we specify a default value to the parameters to make it optional.

Let's look at an example.

--Optional parameter in Stored Procedure  
CREATE PROCEDURE spOptionalParameter  
@Name NVARCHAR(50) = NULL,  
@Gender NVARCHAR(10) = NULL,  
@City NVARCHAR(50) = NULL,  
@DepartmentId INT = NULL  
AS  
BEGIN  
SELECT * FROM tblEmployees WHERE   
(Name = @Name OR @Name IS NULL)   
AND (Gender = @Gender OR @Gender IS NULL)  
AND (City = @City OR @City IS NULL)  
AND (EmployeeDepartmentId = @DepartmentId OR @DepartmentId IS NULL)  
END  

We have created a Stored Procedure spOptionalParameter. In the Stored Procedure, there are four input parameters, and in all these input parameters, we passed a default value of null to make it optional.

1. If we don't pass any value for the parameters, the WHERE clause will be null, and that will give us all the records from the table.

EXECUTE spOptionalParameter

 

2. Let's say we want the details of the employee whose name is Lara.

EXECUTE spOptionalParameter 'LARA'

Look at the IntelliSense we got; the first parameter is @Name, which is assigned to a default value of null. But if we want the records of an employee by name, then we can override this default value and pass the employee's name.

` 

So, now only Lara's record is displayed.

3. Now let's say we want the records of only those employees whose gender is Male.

EXECUTE spOptionalParameter @GENDER = 'MALE'

 

Look at the order of these parameters. The @Gender parameter comes after @Name, and we only want to filter the records based on gender. So, how can we pass a value for gender without passing any value for @Name?

We can use a named parameter. Named parameters are those that are called by the parameter name, and here, the parameter name is @Gender.

EXECUTE spOptionalParameter @GENDER = 'FEMALE'

 

There is another way to do the same thing. All we need to do is pass null in the first parameter, and then we can pass a value for @Gender.

Advantages of using a Stored Procedure in SQL Server

Here are some of the advantages of using stored procedures:

  1. It is easier to maintain and reuse the code using stored procedures. 
  2. The code is executed faster as it executes in memory, not on disk, as with a conventional method. Also, it increases applications' performance.
  3. Parameters can be passed to the stored procedure, which limits changes that need to be made when you update the procedure or its parameters later on down the road.
  4. Stored Procedures can be encrypted, which can prevent SQL Injection Attacks. 
  5. Stored procedures keep application code cleaner. You don't have to have a bunch of SQL stored in your applications. 

Stored procedure vs. Inline SQL

  1. Stored procedures are stored in the database, whereas inline SQL is not.
  2. Stored procedures can have names that can be used to call them. Inline SQL does not have a name because it's embedded in the code where you define it.
  3. Inline SQL does not take arguments or return values as stored procedures do.
  4. Inline SQL can be copied by any developer who has access to the code, while stored procedures are available to limited developers.
  5. Inline SQL can be exposed to hackers if written directly on front-end pages.
  6. If any database changes are made, an application must be updated with new SQL statements and redeployed. 
  7. Multiple applications need duplicate inline SQL. 

Best Practices to Write Stored Procedures

It's always a good idea to follow best practices when writing any code including stored procs. Here is an article, Good Practices to Write Stored Procedures.

How do you increase the performance of a stored procedure?

Many stored procedures (the proc) have a bunch of SQL statements and if the SQL is not optimized or the data size is large, or the tables are not optimized, it may affect the performance of a stored proc. There are several ways to increase the performance of stored procs. Here are some Useful Tips to Increase the Performance of Stored Procedures.

Conclusion

In this article, we have covered what stored procedures are and how to create them in SQL Server. We also discussed the benefits of using stored procedures over other types of code. You can now use stored procedures when writing your queries to make sure that they are reusable and easy to manage. 

If you would like to use SSMS, here is a detailed tutorial on How to work with Stored Procedures using SSMS.


Similar Articles