What are Stored Procedures?

Stored Procedures can be defined as a collection of logically related SQL statements that are wrapped under a name given to the stored procedure to perform a specific task. These SQL statements are compiled and wrapped as a stored procedure so whenever these statements are required, then only the stored procedure is executed and there is no need for writing the set of statements again and again but instead the stored procedure is called. A stored procedure can even accept input as parameters and may or may not return values as per the action performed by the stored procedure.

Types of Stored Procedures:

There are following types of stored procedures in SQL:-

• USER DEFINED STORED PROCEDURES:-
  

  User-Defined stored procedures are the ones that are created by the user and stored in the current database

• SYSTEM STORED PROCEDURES:-

  System Stored procedures are the ones that are provided by the SQL for the maintenance of the system. They act as an interface between SQL Server to external programs.

• TEMPORARY STORED PROCEDURES:-
  

  Temporary stored procedures are a type of user-defined stored procedures. But these are further classified into two categories i.e. local and global. Local temporary procedures are visible only to the current connection and they are deleted when the connection is closed. On the other hand, global temporary procedures are visible to any user after they are created.

• EXTENDED USER DEFINED STORED PROCEDURES:-
  

  Extended user-defined procedures are actually the DLLs that an instance of SQL Server can dynamically run but now, CLR procedures are used in place of these procedures.

 System Stored Procedure:

System stored procedures are created by the system for administrative activities and the user does not interfere with these stored procedures but we can have a look to these stored procedures following the below steps:-

Databases -> Programmability -> Stored Procedures -> System Stored procedures

User-Defined Stored Procedure

User-defined stored procedures are the most commonly used stored procedures. They include DML commands like INSERT, DELETE, and UPDATE to perform various functions. Depending upon the parameters the stored procedure will accept parameters as input and return values as output parameters.

Syntax to create a Stored Procedure

CREATE PROCEDURE procedure_name

AS

Sql_statement

Syntax to execute a Stored Procedure

EXEC procedure_name

Creating a Stored Procedure

Suppose we have a table named ‘empl’ consisting the details of an employee

Now if I wish to select the NAME and SALARY of employee every time I would have to write the following query again and again, which would be compiled and executed every time.

select name, salary from Empl

Instead, we can wrap up this query inside a stored procedure thereby increasing the efficiency of a database.

create procedure selectemployees

as

select name, salary from Empl

Exec selectemployees

Note:- It is a naming convention to name the system stored procedures with a prefix ‘sp’. So, it is always advisable to not use this prefix with user-defined stored procedures to avoid ambiguity.

The output to the above query is given below:-

We can even graphically execute or create a stored procedure by right-clicking the STORED PROCEDURE under programmability in the Object Explorer.

Parameters in a Stored Procedure

Parameters are used to exchange values between the stored procedure and the caller. There are two types of parameters:-

• INPUT PARAMETERS:- These parameters have to be provided by the caller to the stored procedure for the completion of the execution of a stored procedure.
• OUTPUT PARAMETERS:- These parameters are returned by the stored procedure after completion of the execution.

Creating a Stored Procedure with input parameters

Consider the ‘empl’ table mentioned above, now we create a stored procedure which takes a parameter ‘DeptId’ as input and shows the employees where the department id matches with the input parameter value. The code for the stored procedure is given below.

create procedure getempl

@DeptId int

 as

 select * from empl where DeptId=@DeptId

Now we need to call the stored procedure but with that, we have to provide the value of the department Id that is taken as the input parameter. So, we can say that these parameters are place holders because when users execute the stored procedure they are going to pass a value for the department id which will be replaced at execution time. If we execute a stored procedure without providing the parameter then it will show an error because the stored procedure is expecting a parameter

exec getempl 222

The result of the above query is mentioned below :

Creating a Stored Procedure with output parameters

To create a stored procedure with output parameters, we use the keyword OUT or OUTPUT. Consider the above employee table. We wish to create a stored procedure that takes an input parameter as the ‘DeptId’ and the count of employee as an output value. So, we write the code as follows

create procedure getemploycount

@DeptId int,

@EmployCount int Output

as

begin

select @EmployCount=count(Id) from empl where DeptId=@DeptId

end

In the above query, @DeptId is the input parameter and @EmployCount is the output parameter. From the above-stored procedure we try to get the count of the employees whose department id matches the input value provided by the user and the count of employees with the corresponding department id is returned as the output. We are initializing the @EmployCount, the output parameter with the count returned by the stored procedure.

Now, we have created the stored procedure but have to execute it. The stored procedure with output parameters is executed in a different way. As an output value will be returned by the stored procedure so we need a variable to hold that value. Here we declare the variable ‘totalcount’ to receive the value returned by the stored procedure. After that, we pass the input i.e. 222, and a variable to store the value returned by the stored procedure, and finally, the value is printed.

declare @totalcount int

exec getemploycount 222, @totalcount out

print @totalcount

OUTPUT of the above query.

Here the first picture shows the table before the query and finally produces the output i.e. the count of employees with DeptId=222 in the second picture.