Stored Procedure in SQL Server with Examples

In this article, I am going to discuss what exactly is Stored Procedure in SQL Server and what are the advantages of using Stored Procedure as compared to simple SQL Statements. As a SQL Server developer, it is the most important concept for you to understand. As part of this article, we are going to discuss the following pointers related to the Stored Procedure.

Why we need a Stored Procedure in SQL Server?

Before going to understand why we need Stored Procedure, let us first understand what happens when we execute a simple SQL Statement on SQL Server. When any SQL Statements fired on SQL Server, then three steps are happening in order which is shown in the below image.

Syntax Checked:

This step ensures that the syntaxes are correct and there is no error and it is ready for executing on SQL Server.

Plan Selected:

Once the syntaxes are checked, the second step is to select a plan. The SQL Query must be using some tables. It will go and check what types of indexes are exists on these tables, it will also check can use these indexes or a table scan is fine. So, the second step is to select a proper plan to execute the query.

Query Execution:

Once the plan is selected, the final step is to execute the query and the output is seen by the end-user.

So, any SQL Statement fire on SQL Server should go through on these three steps.

Now somehow, if we ensure that the first two steps (i.e. Syntax Checked and Plan Selected) are executed only once, would not it be great. In other words, the first time the SQL is executed, the syntaxes are checked, the plan is selected and the plan is cached in memory. So, if the same SQL statements executed again, then these two steps are not going to be executed, rather the plan is taken from the cache and executed and that will definitely increase the performance of the application which is shown in the below image.

This what exactly stored procedure does in SQL Server. When we create a stored procedure, the syntaxes are checked while creating the procedure or we can say at the design pattern. When we execute the procedure for the first time, the best plan is selected and is cached in memory. And after that whenever we execute the stored procedure, the query execution plan is taken from the cache rather than creating again and again and executed.

Along with there are also more other advantages of using stored procedures which we will discuss in our upcoming articles. With this keep in mind, let us proceed and understand the SQL Server Stored Procedure in detail.

What is a Stored Procedure in SQL Server?

A SQL Server Stored Procedure is a database object which contains pre-compiled queries (a group of T-SQL Statements). In other words, we can say that the Stored Procedures are a block of code designed to perform a task whenever we called.

How can we create a Stored Procedure in SQL Server?

In SQL Server, you can create a stored procedure by using the CREATE PROCEDURE or CREATE PROC statement. Again, you can create a procedure with or without parameters. Please have a look at the below image for the Syntax of Stored Procedure.

SP without Parameter:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

SP with parameter:

-- Transact-SQL Syntax for Stored Procedures in SQL Server and Azure SQL Database

CREATE [ OR ALTER ] { PROC | PROCEDURE }

    [schema_name.] procedure_name [ ; number ]

    [ { @parameter [ type_schema_name. ] data_type }

        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]

    ] [ ,...n ]

[ WITH <procedure_option> [ ,...n ] ]

[ FOR REPLICATION ]

AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

[;]

<procedure_option> ::=

    [ ENCRYPTION ]

    [ RECOMPILE ]

    [ EXECUTE AS Clause ]

A stored procedure is very much similar to a function in c, c++ languages or a method in java or .net languages. The procedure definitions contain two parts in it

1. Procedure header
2. Procedure body

The content above “AS” is known as procedure header and the content below the “AS” is known as procedure body. If required we can pass the parameter to a procedure to make the procedure more dynamic.

How to call a Procedure in SQL Server?

Once we create the stored procedure, then it is physically stored on the server as a “database object” which can be called from anywhere connecting to the server.

We can call the procedure from anywhere that is from a new query window or from any application that is developed using java or .net language also in three different ways as shown in the below image.

 

Execute ProcedureName Values

OR

EXEC ProcedureName Values

OR

ProcedureName Values

 

Note: Another way to execute a stored procedure is to right-click on the procedure name in object explorer and select “Execute Stored Procedure”.

In this article, we will create the Stored Procedure Without Parameters and in our next article, we will discuss Creating Stored Procedure with input parameters.

Stored Procedure in SQL Server Without Parameter

The following stored procedure simply a welcome message on the screen

CREATE PROCEDURE spDisplayWelcome

AS

BEGIN

  PRINT 'WELCOME TO PROCEDURE in SQL Server'

END

Calling a Stored Procedure:

EXECUTE spDisplayWelcome

Or

EXEC spDisplayWelcome

Or

spDisplayWelcome

 

Output: WELCOME TO PROCEDURE in SQL Server

Note: While naming the user-defined stored procedures we should not have to use “sp_” as a prefix as it is recommended by Microsoft. The reason is all the system-defined procedures in SQL Server are prefixed with “sp_”. So to avoid the ambiguity between the user-defined stored procedure and the system stored procedures and for any conflicts with some future coming system procedure we should not use sp_ as a prefix to our user-defined stored procedure.

Let’s see another example where we will fetch the data from a database table.

We are going to use the following Employee table.

Please use the following SQL Script to create and populate the Employee table with the required sample data.

-- Create Employee Table

CREATE TABLE Employee

(

  ID INT PRIMARY KEY,

  Name VARCHAR(50),

  Gender VARCHAR(50),

  DOB DATETIME,

  DeptID INT

)

GO

-- Populate the Employee Table with test data

INSERT INTO Employee VALUES(1, 'Pranaya', 'Male','1996-02-29 10:53:27.060', 1)

INSERT INTO Employee VALUES(2, 'Priyanka', 'Female','1995-05-25 10:53:27.060', 2)

INSERT INTO Employee VALUES(3, 'Anurag', 'Male','1995-04-19 10:53:27.060', 2)

INSERT INTO Employee VALUES(4, 'Preety', 'Female','1996-03-17 10:53:27.060', 3)

INSERT INTO Employee VALUES(5, 'Sambit', 'Male','1997-01-15 10:53:27.060', 1)

INSERT INTO Employee VALUES(6, 'Hina', 'Female','1995-07-12 10:53:27.060', 2)

GO

 

Create a stored procedure to get the names, gender, and the dob of all employees from the table Employee table.

CREATE PROCEDURE spGetEmployee

AS

BEGIN

  Select Name, Gender, DOB from Employee

END

-- To Execute the Procedure

EXEC spGetEmployee

 

When we execute the above statement it will give us the below output.

 

How to View the text of a Stored Procedure in SQL Server?

Once you created the stored procedure and later if you want to view the text of the stored procedure then you need to use the sp_helptext system-defined stored procedure by supplying the procedure name as a parameter as shown below

Example: sp_helptext spGetEmployee

Else just right click on the stored procedure in object explorer Script procedure as Create To new query editor window

How to change the name and body of a stored procedure in SQL Server?

CREATE PROCEDURE spGetEmployee

As

BEGIN

  SELECT Name,Gender, DOB FROM Employee

END

-- How to change the body of a stored procedure

-- User Alter procedure to change the body

ALTER PROCEDURE spGetEmployee

AS

BEGIN

  SELECT Name, Gender, DOB 

  FROM Employee 

  ORDER BY Name

END

-- To change the procedure name from spGetEmployee to spGetEmployee1

-- Use sp_rename system defined stored procedure

EXEC sp_rename 'spGetEmployee', 'spGetEmployee1'

 

How to Drop a Stored Procedure?

In order to drop a stored procedure, all you need to use the following syntax

DROP PROCEDURE ProcedureName

Example: Drop proc spGetEmployee1 or Drop Procedure spGetEmployee1

 

In the next article, I will discuss the Stored Procedure with input parameters. Here, in this article, I try to explain the Stored Procedure in SQL Server with some examples. I hope now you understand the need and use of Stored Procedure.