Stored Procedure in SQL Server With Examples

What Is a Stored Procedure In SQL Server?

 Stored Procedure : A stored procedure in SQL Server is a precompiled collection of SQL statements that get cached and stored in the database in order to reused over and over again whenever required.

Advantage of using Stored Procedure

    • A stored procedure (SP) provides the reusability of SQL code as we can simply call the stored procedure Instead of writing the same SQL statements over and over again.
    • It allows reducing the network traffic by executing the SQL statements at the server-side rather than sending hundreds lines of SQL queries over the network.
    • A stored procedure can be used as a security mechanism by granting a user to execute only the stored procedure instead of allowing permission on the table.
    • It allows faster execution of SQL statements in order to enhance the performance.

Syntax of Stored Procedure

Following is the syntax to use the stored procedure.

CREATE PROCEDURE ProcedureName
AS
BEGIN
-- SQL Statements
END

-- OR
CREATE PROC ProcedureName
AS
BEGIN
-- SQL Statements
END

Syntax to execute a stored procedure

EXEC ProcedureName;

--OR

EXECUTE ProcedureName

Before creating and executing any stored procedure let’s first create a table ‘EmployeeDetails’ and insert some records.

Create a Table

-- 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');



Create a stored procedure

Now let’s create a stored procedure to get all the records from the table [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.

sql-stored-procedure-in=database-shekh ali
Stored procedure location in the database

Execute a stored procedure

-- Execute the stored procedure
EXECUTE spGetAllEmployees;

Once we execute the stored procedure, we will get the following result.

sql stored procedure

We can also create, modify, or execute the stored procedure using the SQL server management studio.

Execute sql stored procedure
Execute the stored procedure

Create a stored procedure with an input parameter

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

stored procedure with input parameter

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 with an output parameter

In SQL, A stored procedure can have single or multiple output parameters of various data types.

Following is the syntax.

-- 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 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 are using 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.

output variable

Create a stored procedure to INSERT 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.

Insert record using stored procedure

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 to update the existing record based on employee Id.

-- Execute the stored procedure to update the record

Execute spUpdateEmployeeById 5,'Julian','Male','France';

After executing the stored procedure the record with Id 5 is updated.

update using stored procedure

Create a stored procedure to DELETE records

Now let’s create a stored procedure to delete an employee record based on the Id column. 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 have discussed How to create a stored procedure with input and output parameters. How to INSERT, UPDATE, or DELETE the record using the user-defined stored procedure.

In my previous article, We have learned about the Types of SQL Commands.

Hope you enjoyed this post. Thanks for visiting.

Leave a Reply

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

seven − one =