Stored Procedure in SQL With Example - Create, Update, Delete

SQL Stored Procedure (Create, Update, Delete)

How to Create Stored Procedure in SQL?

Stored Procedure in SQL with example

What is the Stored Procedure in SQL?

A stored procedure is a prepared SQL (Structured Query Language) Statement that you can save, you can reuse the statement over and over again.

"Stored procedure is a block of code which is given a name and stored in the database" This block of code can include

SQL Queries
DML, DDL, DCL, and TCL
Collection types
Variable

Etc.

The procedure can not only be used to query data from the table we can use the procedure to build complex logic data validations data cleanup and much more.

Syntax of Stored Procedure -Creating Procedure

The basic syntax to create a stored procedure is given below.

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Syntax of Stored Procedure -Executing Procedure

The basic syntax to execute a stored procedure is given below.

EXEC procedure_name;

Example of Stored Procedure

A basic example of the stored procedure is given below.

CREATE PROCEDURE SelectAllEmployee
AS
SELECT * FROM Employee
GO;

Explanation

The above SQL Query example creates a stored procedure named "SelectAllEmployee" that selects all records from the "Employee" table.

Execute Stored Procedure

EXEC SelectAllEmployee;

Types of Stored Procedures

In SQL Stored procedures are divided into 4 subcategories.

System Defined Stored Procedure.
Extended Procedure.
User-Defined Stored Procedure.
CLR Stored Procedure.

How to Create a Stored Procedure in SQL?

We will consider below employee table to create the stored procedure.

How to create a stored Procedure in SQL

Suppose we want the list of all employees from the employee table instead of writing SQL Statement (Select * from Employee;) we are going to create a stored procedure for the same.

CREATE PROCEDURE SelectAllEmployee
AS
SELECT * FROM Employee
GO;

Explanation

The above SQL Query example creates a stored procedure named "SelectAllEmployee" that selects all records from the "Employee" table.

How To Execute a Stored Procedure?

To Execute the stored procedure in SQL check the below example.

Execute a Stored Procedure

EXEC SelectAllEmployee;

How To Modify or Update Stored Procedures?

To modify a stored procedure just follow the below example.

ALTER PROCEDURE SelectAllEmployee
AS
SELECT * FROM Employee where EmployeeDept='IT'
GO;

The above-stored procedure will select all the employees whose department is IT.

How to Rename Stored Procedure in SQL?

In SQL we can rename the procedure in two ways.

Method-1

Using sp_rename we can change the stored procedure name.

Syntax to rename a stored procedure

EXEC sp_rename 'Current procedure_name', 'New procedure_name';

Example

EXEC sp_rename 'SelectAllEmployee', 'EmployeeByDept';

Method-2

From Object Explorer also we can change the name of the stored procedures manually by just following the below steps.

Steps-1
Right-click on the procedure created and select the ‘Rename’ option.

Steps-2

Type the name with which you want to rename the procedure.

By following the above two methods we can rename the stored procedure in SQL.

How to Delete a Stored Procedure in SQL?

Method 1 (Using Query )

Syntax

Drop Procedure/PROC YourProcedureName

Drop Procedure/PROC dbo.YourProcedureName

Method 2 (Object Explorer)

Open Object Explorer in SQL
Open Databases
Select your desired database
Select Programmability
Select Stored Procedures
From the list of stored procedures, Right click on the stored procedure you want to remove
Click on Delete and the stored procedure will be removed

You can also view dependencies of that stored procedure before deleting, by clicking on View Dependencies

Example to Delete or Drop a Stored Procedure (SP)

DROP PROCEDURE dbo.uspMyProc;
GO

Why do create a Stored procedure?

In SQL a stored procedure is created to perform one or more DML (Data Manipulation Language) operations on the database.

SQL Provides some benefits which are listed below.

Reusable
Easy to Modify
Security
Low network traffic
Increase performance

When to create a stored procedure in SQL?

Who Creates Stored Procedures in SQL?

Generally, SQL-stored procedures are created by Database developers, database administrators, or any backend developer having knowledge of databases.

Stored Procedure with Parameter

How to Create Stored Procedure With Parameter?

Stored Procedure with one Parameter

CREATE PROCEDURE SelectAllEmployee @City nvarchar(30)
AS
SELECT * FROM Employee WHERE City = @City
GO;

Explanation

The above SQL statement creates a stored procedure that selects Employee from a particular City from the "employee" table:

Execute Above Stored Procedure

EXEC SelectAllEmployee @City = 'Lucknow';

Stored Procedure with Multiple Parameters

CREATE PROCEDURE SelectAllEmployee @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Employee WHERE City = @City AND PostalCode = @PostalCode
GO;

Explanation

The above SQL statement creates a stored procedure that selects Employee from a particular City with a particular PostalCode from the "Employee" table:

Execute Above Stored Procedure