Stored Procedure In SQL Server| Types Of Stored Procedure In SQL

Last updated on February 9th, 2022 at 04:04 pm

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

1. 1. 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.
   2. 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.
   3. 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.
   4. 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.

Conclusion

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.

FAQs

Hope you like this article and find it useful. If you have any questions, please put your comments below.

Recommended Articles

1. • 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

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024
• How to Reverse an Array in C# with Examples - February 12, 2024