In this article, we will discuss how to create a stored procedure in a SQL Server database. Also, we will try to understand the different types of stored procedures available in SQL Server through various examples.
What Is a Stored Procedure In SQL Server?
Stored procedures in SQL Server are precompiled collections of SQL statements that are cached and stored in the database so that they can be reused repeatedly as needed. Stored procedures are created on the SQL server and used to perform one or more DML operations on objects in the database. It may or may not return a value.
Advantage of using Stored Procedure in SQL Server
The following are the advantage of using stored procedures in SQL Server.
- Stored Procedures (SP) provide the ability to reuse SQL code because we can simply call stored procedures instead of writing the same SQL statement over and over again.
- Stored procedures are precompiled SQL statements, so you don’t have to compile them every time. It reduce network traffic by executing batches of SQL statements in a single execution plan. The stored procedure is also cached on the server side.
- Stored procedures can be used as a security mechanism by granting users only permission to execute stored procedures instead of allowing permissions on tables.
- It allows faster execution of SQL statements in order to enhance the performance.
- Stored procedures in SQL Server improve scalability by isolating application processing on the server.
TYPES OF STORED PROCEDURES IN SQL SERVER
- User defined Stored Procedure: The User-defined stored procedures in SQL Server are created by database developers or database administrators in user-defined database. These stored procedures contain one more SQL statements to select, update, insert, or delete records from database tables.
- System Defined Stored Procedure: These stored procedures are already defined and physically stored into SQL Server hidden Resource database and logically we can find in the sys schema of every user-defined and system-defined database. It basically starts with the sp_ prefix. Example: sp_rename, sp_help, sp_helpdb, sp_helptext etc.
- Temporary Stored Procedure: Temporary Stored Procedure is of two types Local temporary stored procedure and Global temporary stored procedure. It is also type of user-defined stored procedure which is stored in tempdb database.
A local temporary stored procedure in SQL Server is available only in the current user session and is dropped automatically when the session is closed. A global temporary stored procedure is available to all sessions and is dropped when the session of the user that created it is closed.
- Remote Stored Procedure: Remote stored procedures are a legacy feature of Microsoft® SQL Server™ 2000. Remote stored procedure basically created and stored on remote server database,these remote procedure can be accessed from other server.
Syntax of Stored Procedure
Following is the syntax used to create a stored procedure in the SQL server.
CREATE PROCEDURE ProcedureName AS BEGIN -- SQL Statements END -- OR CREATE PROC ProcedureName AS BEGIN -- SQL Statements END
Syntax to execute a stored procedure in SQL
Following is the syntax to execute the stored procedure.
EXEC ProcedureName; --OR EXECUTE ProcedureName
Before creating and executing any stored procedure, let’s create a table ‘EmployeeDetails’ and insert some records.
How to Create a Table in SQL Server?
Let’s look at the following example to see how to create a table in the SQL server.
-- Create a new table [EmployeeDetails] CREATE TABLE EmployeeDetails ( [Id] INT PRIMARY KEY, [Name] VARCHAR(50), [Gender] VARCHAR(10), [City] VARCHAR(100) ); -- Insert some records in the table [EmployeeDetails] INSERT INTO EmployeeDetails VALUES(1,'Shekh Ali','Male','Ballia'); INSERT INTO EmployeeDetails VALUES(2,'Jyoti','Female','Noida'); INSERT INTO EmployeeDetails VALUES(3,'Rajat','Male','Jaipur'); INSERT INTO EmployeeDetails VALUES(4,'Ajay', 'Male','Mumbai');
How to Create a ‘stored procedure in SQL’?
Let’s take a look on the following example to see how to create a stored procedure to get all the records from the table [EmployeeDetails] EmployeeDetails
-- Create a stored procedure to get all the selected records CREATE PROCEDURE spGetAllEmployees AS BEGIN SELECT Id, Name, Gender, City FROM EmployeeDetails; END;
When we execute the above SQL statement a new stored procedure spGetAllEmployees will be created in the database in the following location.
Select database > go to Programmability > Stored Procedures > Right-click and refresh the folder.
Execute a stored procedure in SQL Server
The following is an example of how to execute a stored procedure in the SQL server.
-- Execute the stored procedure EXECUTE spGetAllEmployees;
Once we execute the stored procedure, we will get the following result.
We can also create, modify, or execute the stored procedure using the SQL server management studio.
Stored procedure with an input parameter in SQL Server
Let’s create a stored procedure with an input parameter to get the records based on employee Id.
-- stored procedure with input parameter an input parameter CREATE PROCEDURE spGetEmployeeById (@Id AS INT) AS BEGIN SELECT Id, Name, Gender, City FROM EmployeeDetails WHERE Id= @Id ; END;
In the above SQL stored procedure, we have added a parameter named @Id of integer type.
We are using @Id parameter in the WHERE clause of the SELECT statement to filter only the employee record whose Id is equal to the @Id parameter.
Note: Parameters in stored procedure must start with the @ prifix.
To execute the “spGetEmployeeById ” stored procedure, we need to pass an argument as below.
--- Pass an input parameter EXECUTE spGetEmployeeById 1
Here in the above result, we can see that the stored procedure returns only that record whose Id is equal to 1.
Create a stored procedure in SQL server with an output parameter
In SQL, A stored procedure can have single or multiple output parameters of various data types.
Following is the syntax to create a stored procedure with an output parameter.
-- Syntax for the output parameter ParameterName DataType OUTPUT
In the following example, we are creating a stored procedure “spTotalEmployees ” to get the total number of employees from the table using an output parameter.
In this Stored procedure, we have declared a variable “@EmployeeCount” of integer Type with OUTPUT keyword.
-- Stored procedure with an output parameter CREATE PROCEDURE spTotalEmployees ( -- output parameter @EmployeesCount INT OUTPUT ) AS BEGIN SELECT @EmployeesCount = COUNT(*) FROM EmployeeDetails; END;
Once we execute the above SQL statement, a new stored procedure will be created in the database.
Calling SQL stored procedures with an output parameter
To call a stored procedure having output parameters, we must declare variables to hold the values returned by the output parameters.
In the following example, we use only one output parameter called @Count to hold the total numbers of employees returned by the stored procedure.
-- Declare output variable DECLARE @Count int ; Execute spTotalEmployees @EmployeeCount = @Count output; SELECT @Count AS 'Total Number of Employees';
Once we execute the above statements, we will get the result as below image.
Create a stored procedure in SQL to add new records
In this example, we are creating a stored procedure to insert a new record into the table. The stored procedure will contain 4 input parameters such as @Id, @Name, @Gender, and @City.
-- Stored procedure to Insert new record into the table CREATE PROCEDURE spAddNewEmployee ( -- Input parameters @Id INT, @Name VARCHAR(50), @Gender VARCHAR(10), @City VARCHAR(100) ) AS BEGIN INSERT INTO EmployeeDetails (Id, Name, Gender,City) VALUES (@Id,@Name, @Gender,@City) ; END;
When we execute the above statements, a new stored procedure will be created in the database.
Now let’s insert a new record by passing the following input parameters in the stored procedure called “spAddNewEmployee“
-- Insert record using stored procedure EXECUTE spAddNewEmployee 5, 'Sandeep','Male','Delhi';
After executing the stored procedure a new record will be inserted into the table.
Create a stored procedure to UPDATE records
Now we will create a new Stored procedure to update the record in a table based on the Id column.
Following is the example.
-- Stored procedure to Update the records CREATE PROCEDURE spUpdateEmployeeById ( -- Input parameters @Id INT, @Name VARCHAR(50), @Gender VARCHAR(10), @City VARCHAR(100) ) AS BEGIN UPDATE EmployeeDetails Set Name = @Name, Gender = @Gender, City = @City WHERE Id = @Id END ;
When we execute the above statements, a new stored procedure will be created in the database.
Now let’s execute the stored procedure in the SQL server to update the existing record based on employee Id.
-- Execute the stored procedure in SQL Server to update the record Execute spUpdateEmployeeById 5,'Julian','Male','France';
After executing the stored procedure the record with Id 5 is updated.
Create a stored procedure in SQL to DELETE records
Now let’s create a stored procedure to delete an employee record based on the Id. Following is the example.
-- Stored procedure to delete the record CREATE PROCEDURE spDeleteEmployeeById ( -- Input parameters @Id INT ) AS BEGIN DELETE FROM EmployeeDetails WHERE Id = @Id END ;
After executing the above SQL statement a new stored procedure will be created in the database.
Now let’s execute the following SQL statements to delete a record where Id is equal to 5.
-- Delete a record using stored procedure EXECUTE spDeleteEmployeeById 5 ;
We can also execute the stored procedure to Delete the records using the SQL server management studio by entering the parameter’s value in the popup window as below image.
Select database > go to Programmability > Stored Procedures > Right-click and refresh the folder > Select the stored procedure > Right-click and select Execute Stored Procedure.
In this article, we learned about, How to create a stored procedure in the SQL server with input and output parameters. How to INSERT, UPDATE, or DELETE the record using the user-defined stored procedure. We also learned about the different types of stored procedures in SQL Server.
Q: What are the different types of stored procedures in SQL Server?
Ans: The following are the different types of stored procedures available in SQL Server.
1. System Defined Stored Procedure
2. User-Defined Stored Procedure
3. CLR Stored Procedure
4. Extended Procedure
Q: What are stored procedure parameters?
Ans: In stores procedure, parameters are used to pass input and return output values.
Parameters can be divided into two categories:
1. Input parameters: Used to pass values to the stored procedure.
2. Output parameters: Used to return values from the stored procedure.
Q: Why do we need to SET NOCOUNT ON in a stored procedure?
Ans: When the SET NOCOUNT ON command is used in the stored procedure, No messages indicating the number of rows affected will be displayed to the user.
Q: Where is stored procedure saved in SQL Server?
Ans: Stored procedures are reside within following location: SQL Server Studio > Object Explorer > Database > Programmability Directory📁
Hope you like this article and find it useful. If you have any questions, please put your comments below.
- Different types of SQL Joins
- How To Create, Alter, and Drop Database In SQL Server
- SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators
- Primary Key Constraint In SQL Server
- View In SQL | Types Of Views In SQL Server
- SQL Delete Cascade
- SQL Server Trigger Update, Insert, Delete Examples
- Types of SQL Commands