Stored Procedure Explained

SQL Stored Procedure Explained

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 as explained in SQL Server Tutorial site

This post introduces you to the stored procedures and shows you how to develop flexible stored procedures to optimize your database access whether with C# or SQL Server Management Studio.

Connect to SQL Server through Microsoft SQL Server Management Studio as shown below

Stored Procedure Syntax (Create)

CREATE PROCEDURE procedure_name
AS

BEGIN
sql_statement
END

EXC procedure_name

Explanation of the syntax

CREATE is a keyword in Transact sql for creating new stored procedure.

PROCEDURE is also a keyword in Transact SQL that signifies creating a stored procedure. We can also use PROC keyword instead of the long name.

AS keyword is to specify the transact sql statements or command logics.

BEGIN and END is use to wrap the t-sql statements if we have more than one, but can be omitted if it’s only one statement.

We are going to be using the csharpnaija_employees database and employees, departments tables created from previous post.

Employees table

Departments table

 In this post, we will learn how to manage stored procedures in SQL Server including creating, executing, modifying, and deleting stored procedures.

Creating a Stored Procedure

For instance, if we want to get records from employees table, we use the below code

SELECT * FROM employees                      

But if we are going to be using this statement in several places, we create a stored procedure that encapsulate the above statement so as to reuse the code.

To create a stored procedure that wraps this query, you use the CREATE PROCEDURE statement as follows

We can graphically create this stored procedure in MSSMS as outlined below

Expand csharpnaija_employees database from object explorer as below

Expand the programmability folder under the csharpnaija_employees as shown below

Right click on Stored Procedures folder, Select New and click on Stored Procedure as shown below

From the new stored procedure template automatically created, change the procedure name, parameters and the statement as shown below

Change the template to look like the below

-- =============================================

-- Author:           <Musa Sule>

-- Create date: <20/01/2020>

-- Description:      <Get All Employees with their respective departments>

-- =============================================

CREATE PROCEDURE GetAllEmployees

AS

BEGIN

       SET NOCOUNT ON;

       SELECT e.FirrstName,e.MiddleName,e.LastName,e.PhoneNumber,e.Address, d.DeptName

       FROM employees e

       INNER JOIN departments d

       ON e.DepartmentId=d.Id

END

GO

Click on Execute  to create and save the procedure for later use

If everything is ok, Commands completed successfully as shown in the Messages window below

Now expand the stored procedures folder to see the newly created procedure as shown below

To execute the created procedure, we call EXECUTE sp_name;

EXEC GetAllEmployees

Modifying a stored procedure

To modify an existing stored procedure, you use the ALTER PROCEDURE statement.

First, open the stored procedures to view its contents by right-clicking the stored procedure name you want to modify and select Modify as shown below

The stored procedure to be modified will open in query window as shown below

Change anything you want to change from the T-SQL statements and click on Execute as shown below

The result of the modified stored procedure is as shown below

Deleting a stored procedure

To delete a stored procedure, we will use the DROP PROCEDURE or DROP PROC statement in the query window.

Syntax

DROP PROCEDURE sp_name; or DROP PROC sp_name;

We can also use the Graphical User Interface in the Microsoft SQL Server Management Studio

Right click on the stored procedure you want to delete as shown below   

A Delete Object window will be displayed, click on OK to Delete the stored Procedure as shown below

The stored procedure will deleted and removed from the Stored Procedure folder.