Stored Procedure in SQL Server with Examples
In this article, I am going to discuss the Stored Procedure in SQL Server with some examples. 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.
- What is a SubProgram in SQL Server?
- What is a Stored Procedure in SQL Server?
- Why do we need the Procedure in SQL Server?
- How many ways can we create a Stored Procedure?
- How to call a Stored Procedure?
- Examples of SQL Server Stored Procedure.
- How to view the text of a Procedure in SQL Server?
- How to Drop and Alter a Procedure in SQL Server?
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
- Stored procedure/procedures
- Stored functions / functions
- Database triggers
What is a Stored Procedure in SQL Server?
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 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 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 a 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 Stored Procedure.
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
- Procedure header
- 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.
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 the Stored Procedure Without Parameters and in our next article, we will discuss Creating Stored Procedure with input parameters.
SQL Server Stored Procedure without Parameter
Example1: Create a 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:
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
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.
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.