SQL Server - Stored Procedures

In SQL Server, a stored procedure is a set of T-SQL statements which is compiled and stored in the database. The stored procedure accepts input and output parameters, executes the SQL statements, and returns a result set if any.

By default, a stored procedure compiles when it gets executed for the first time. It also creates an execution plan that is reused for subsequent executions for faster performance.

Stored procedures are of two types:

User-defined procedures: A User-defined stored procedure is created by a database user in a user-defined database or any System database except the resource database.

System procedures: System procedures are included with SQL Server and are physically stored in the internal, hidden Resource database and logically appear in the sys schema of all the databases. The system stored procedures start with the sp_ prefix.

Create Stored Procedure

Use the CREATE statement to create a stored procedure.

CREATE [OR ALTER] {PROC | PROCEDURE} [schema_name.] procedure_name([@parameter data_type [ OUT | OUTPUT | [READONLY]] 
[ WITH <procedure_option> ]
[ FOR REPLICATION ]
    AS
    BEGIN
        sql_statements 
    END

A stored procedure can contain one or more select, insert, update, or delete statements. The following is an example of a simple stored procedure that returns the records from the Employee table using the SELECT query.

CREATE PROCEDURE uspGetEmployeeList
AS
BEGIN
   SELECT EmpID
	 ,FirstName
	 ,LastName
   FROM dbo.Employee
END

Execute the above T-SQL script in the query editor to compile and create it in the database, as shown below.

The above stored procedure can be executed using the EXEC keyword, as shown below.

The following stored procedure inserts values in the Employee table.

CREATE PROCEDURE dbo.uspInsertEmployee
(
 @FirstName nvarchar(50)
,@LastName nvarchar(50)
,@Email nvarchar(50)
,@PhoneNo nvarchar(20)
,@Salary money
)
AS
BEGIN
	INSERT INTO dbo.Employee
           (FirstName
           ,LastName
           ,Email
           ,PhoneNo
           ,Salary)
    VALUES
	(
		@FirstName
		,@LastName
		,@Email
		,@PhoneNo
		,@Salary
	)
 END

The above stored procedure can be used to insert values to the Employee table instead of the INSERT statement. Values are passed as parameters to the stored procedure. The @ symbol is used as a prefix for parameter variables.

You can execute the uspInsertEmployee stored procedure using the EXEC keyword, as shown below.

EXEC dbo.uspInsertEmployeeDetails
@FirstName ='Swati'
,@LastName = 'Karia'
,@Email = '[email protected]'
,@PhoneNo = '6657890980'
,@Salary = 300000

Specify each parameter separated by a command while executing a stored procedure.

View Stored Procedure

Use sp_help or sp_helptext to see the text of an existing stored procedure, as shown below.

All the stored procedures are listed under Programmability > Stored Procedures folder under the database.

Modify Stored Procedure

Use the ALTER PROCEDURE statement to modify a stored procedure.

ALTER PROCEDURE dbo.uspGetEmployees
AS
BEGIN
   SELECT EmpID
	 ,FirstName
	 ,LastName
     ,Salary
   FROM dbo.Employee
END

Renaming Stored Procedure

Use the system stored procedure sp_rename to rename an existing stored procedure. The following renames uspGetEmployeeList to uspGetEmployees.

sp_rename 'uspGetEmployeeList','uspGetEmployees'

Delete Stored Procedure

Use the DROP PROCEDURE statement to delete a stored procedure.

DROP PROCEDURE dbo.uspGetEmployees;

Handling Exceptions in Stored Procedures

In SQL Server, the TRY..CATCH block is used to handle exceptions gracefully. A group of T-SQL statements can be enclosed in a TRY block. If an error is encountered in the TRY block, the control is then passed to the CATCH block which will have another set of SQL statements to handle the error.

In the CATCH block, system functions like ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY() can be used to get information about an error.

The following example handles the error in the uspEmpUpdate stored procedure.

CREATE PROCEDURE uspUpdateEmpSalary
(
     @empId int
     ,@salary float
)
AS
BEGIN TRY
    UPDATE dbo.Employee
    SET Salary = @salary
    WHERE EmployeeID = @empId
END TRY
BEGIN CATCH
    SELECT
     ERROR_NUMBER() AS ErrorNumber  
     ,ERROR_SEVERITY() AS ErrorSeverity  
     ,ERROR_STATE() AS ErrorState  
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Advantages of Stored procedures

• Stored procedures are reusable. Multiple users in multiple applications can use the same Stored Procedure (SP)
• As SPs reside in the database, it reduces network traffic. Applications have to make a procedure call to the database and it communicates back to the user.
• Database objects are encapsulated within a stored procedure, and this acts as a security mechanism by restricting access to the database objects.
• Reduced development cost, easily modified, and increased readability.
• Improves performance. When a stored procedure is executed for the first time, the database processor creates an execution plan which is re-used every time this SP is executed.

Learn what is the difference between Functions and Stored Procedure in SQL Server.