Stored Procedures in SQL Server

Stored Procedures in SQL Server

• What a Stored Procedure is
• When to use a Stored Procedure
• How to create a Stored Procedure
• How to create a Stored Procedure with parameters
• How to modify a Stored Procedure
• How to view the text of the Stored Procedure that we created
• How to drop a Stored Procedure
• How to create and execute a Stored Procedures with an output parameter
• Stored Procedures return values
• Difference between Stored Procedures with output parameters and return values
• Stored Procedure with an optional parameter
• Advantages of using a Stored Procedure in SQL Server

What a Stored Procedure is

A Stored Procedure is a collection or a group of T-SQL statements.

When to use a Stored Procedure

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 re-use.

How to create a Stored Procedure

In SQL Server, I have these two tables.



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.

Query

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

Execute it.

 

To get the preceding result, we need to write much code. What if there is a situation where we need to execute the same query again and again. 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 spProcedureName
AS
BEGIN
query
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

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.

Now let's specify the values of these parameters and execute the Stored Procedure.


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 that we created

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 back 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

To ALTER a Stored Procedure we use the ALTER keyword and to drop a Stored Procedure we can use the DROP keyword.

DROP PROCEDURE spEmployeeByGender;

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

To create a Stored Procedure with 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 preceding Stored Procedure that we created. There are two parameters, one is @DepartmentId that is an input parameter and another is the @TotalEmployees parameter that is an output parameter.

The query we wrote inside the Begin and End block is very straight forward. All we are doing is we are filtering the records using a WHERE clause. So, whatever value we pass for the int @DepartmentId parameter, it 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.

To execute this Stored Procedure we can say:

Execute spEmployeeByDepartment


Look at the type of value the first parameter expects, it is of type int. That means we can pass the departmentId here and the second parameter expects an output object.

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 variable_name variable_data_type.
DECLARE @Output INT
Execute spEmployeeByDepartment 1,@Output OUT
SELECT @Output

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 all together.



So, there are 5 employees whose department id is 1.

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

For that all we need to 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 fail.

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



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 output parameter and return value let's create a Stored Procedure.

--difference between out and return
CREATE PROC spOutputById
@Id INT,
@Name NVARCHAR(50) OUTPUT,
@Gender NVARCHAR(10) OUTPUT
AS
BEGIN
SELECT @Name = Name, @Gender = Gender
from tblEmployees WHERE EmployeeId = @Id
END


CREATE PROC spReturnById
@Id INT
AS
BEGIN
RETURN (SELECT Name, Gender from tblEmployees WHERE EmployeeId = @Id)
END

Create two Stored Procedures. 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 block, 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 and Gender of the employee whose Id matches the Id value we pass.

Create both of the Stored Procedures one by one.

When we try to execute the spReturnById Stored Procedure query, we get an error.



Only one expression can be specified in the select list when the sub-query is not introduced and that's the first difference.

So, for now remove the Gender column from the SELECT clause.



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, the return clause is trying to return an nvarchar value back but the return clause can only returns 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 a 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 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 that 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.

In the BEGIN and END block we are retrieving all the columns from the table “tblEmployees” and in the WHERE clause we have four filter conditions. So, we pass a value for any of the parameters. Let's say we pass a value for @Gender. Then this query will execute like this:

SELECT * FROM tblEmployee WHERE @Name IS NULL AND Gender = ‘Male’ AND @City IS NULL AND @DepartmentId IS NULL;

Which will give us all the male records from the table.

Let's look at this practically.

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.



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

So, we can say EXEC spOptionalParameter and give a space as in the following:




Look the intellisense we got, the first parameter is @Name that 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 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.



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.



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

Advantages of using a Stored Procedure in SQL Server

It is very easy to maintain a Stored Procedure and they are re-usable.

The Stored Procedure retains the state of the execution plans.

Stored Procedures can be encrypted and that also prevents SQL Injection Attacks