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 

OR 

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

EXEC SelectAllEmployee @City = 'Lucknow' ,@PostalCode = '222161';

Advantage of stored procedure

Disadvantage of Stored procedure

Hope!!! The above Tutorial on " Stored procedure with Example" is helpful for you...

Team,
QA acharya