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
- Stored procedure/procedures
- Stored functions / functions
- 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.
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 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.
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
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.
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 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
Drop proc spGetEmployee1
Drop Procedure spGetEmployee1
In the next article, I will discuss the Stored Procedure in SQL Server with input parameters.
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.