Stored Procedure in SQL Server

Stored Procedures in SQL Server 

A SQL Server stored procedure groups one or more Transact-SQL statements into a logical unit and is stored as an object in the Database Server. When a stored procedure is called at the first time, SQL Server creates an execution plan and stores it in the plan cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan so that the stored procedure can execute very fast with reliable performance.

Creating a simple stored procedure without any parameters: This stored procedure, retrieves Name and Email_ID of all the employees. To create a stored procedure we use, CREATE PROCEDURE or CREATE PROC statement.

Syntax template for creating stored procedure:

Example:-

To execute the stored procedure, you can just type the procedure name and press F5, or use EXEC or EXECUTE keywords followed by the procedure name as shown below.

1. spGetEmployees

2. EXEC spGetEmployees

3. Execute spGetEmployees

Creating a stored procedure with input parameters: This SP, accepts GENDER parameter. Parameters and variables have an @ prefix in their name.

To invoke this procedure, we need to pass the value for @Gender parameter. If we have multiple parameters then follow sequence of input parameter if we get wrong sequence we will get an error.

EXECUTE spGetEmpDetails 'M'

To create an Stored Procedure with output parameter  we use the keywords OUT or OUTPUT. @EmployeeCount is an OUTPUT parameter. Notice, it is specified with OUTPUT keyword. 

To execute this stored procedure with OUTPUT parameter

1. First initialise a variable of the same datatype as that of the output parameter. We have declared @EmployeeTotal integer variable. 
2. Then pass the @EmployeeTotal variable to the SP. You have to specify the OUTPUT keyword. If you don't specify the OUTPUT keyword, the variable will be NULL. 
3. Execute

Advantages of Stored Procedures:

1. Stored procedures are used so multiple queries execute a single execution.

2. They allow faster execution.

3. Help reduce network traffic

4. Stored procedures can be used for input and output parameters.