SQL Server Stored Procedure

SQL Server Stored Procedure

In this article, I am going to discuss the SQL Server Stored Procedure with some examples. But before understanding the Stored Procedure in SQL Server we need to understand what is a subprogram. So let’s start the discussion with this.

What is a SubProgram in SQL Server?

A subprogram is a named block of code in SQL Server which is saved on the server and can be executed when and where it is required. In SQL Server we have 3 types of subprograms. They are as follows

  1. Stored procedure/procedures
  2. Stored functions / functions
  3. Database triggers
What is SQL Server Stored Procedure?

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

Or we can also define the Stored Procedure in SQL Server as a group of T-SQL (Transact-SQL) statements. If we have a situation where we need to write the same query again and again, we can save that specific query as a stored procedure and then call it’s just by its name whenever required that query to execute.

Why do we need the Stored Procedure in SQL Server?

Whenever we want to execute a SQL Query from an application, the SQL Query (statement) what we send from the application will be first compiled(parsed) for execution. The process of compiling(parsing) is time-consuming because compilation occurs each and every time when we execute the query or statements.

To overcome the above problem we need to write the SQL statements or query as a stored procedure and then execute that stored procedure. A stored procedure in SQL Server is a precompiled block of code that means without compiling(parsing) the statements gets executed whenever the procedure is called which can drastically increase the performance of database server which ultimately increases the performance of the application.

There are many more advantages of using stored procedures which we will discuss in our upcoming articles. In this article, we will discuss how to create, execute, change and delete Stored Procedure in SQL Server database.

How many ways can we create a procedure in SQL Server?

To create a Stored Procedure in SQL Server we need to use the CREATE PROCEDURE or CREATE PROC statement. In SQL Server we can create the stored procedure in two ways i.e. procedure without parameters and procedures with parameters. Please have a look at the below image for the syntax of SQL Server Stored Procedure.

SQL Server Stored Procedure Syntax

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 stored procedure?

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.

SQL Server Stored Procedure

NOTE: We can also right-click on the procedure name in object explorer in SQL Server Management Studio and select “Execute Stored Procedure”.

In this article, we will create Stored Procedure without parameters and in the next article, we will discuss creating Stored Procedure with input parameters.

SQL Server Stored Procedure without Parameter

Example1: Create a Stored Procedure in SQL Server to display the welcome statement on the screen

CREATE PROCEDURE spDisplayWelcome
AS
BEGIN
  PRINT 'WELCOME TO PROCEDURE in SQL Server'
END

Calling a Stored Procedure in SQL Server:

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 stored 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.

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

We are going to use the following Employee table.

SQL Server Stored Procedure

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
Example2: 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.

SQL Server Stored Procedure

How to view the text of the stored procedure?

Once you created the stored procedure and later if you want to view the text of the stored procedure in SQL Server database then you need to use the sp_helptext system defined stored procedure by passing 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 SQL Server?

In order to drop a stored procedure in SQL Server, you need to use the following syntax

DROP PROCEDURE ProcedureName

Example:

Drop proc spGetEmployee1

Drop Procedure spGetEmployee1

In the next article, I will discuss the Stored Procedure in SQL Server with input parameters.

SUMMARY

In this article, I try to explain the SQL Server Stored Procedure without parameters step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

1 thought on “SQL Server Stored Procedure”

  1. I do agree with all the concepts you have offered on your post. They’re very convincing and can certainly work. Still, the posts are very brief for novices. May you please prolong them a bit from next time? Thanks for the post.

Leave a Reply

Your email address will not be published. Required fields are marked *