A STORED PROCEDURES is a set of SQL (Structured Query Language) statements with an assigned name, which are stored in a relational database management system as a group.

SQL Stored Procedures can be reused and shared by multiple programs. So, if you have an SQL query that you write over and over again, save it as a stored procedure, and just call it to execute it.

STORED PROCEDURE Syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute A Stored Procedure Syntax

EXEC procedure_name;

EXAMPLE: STORED PROCEDURE

Employee Table

A sample of EMPLOYEE records table & the name of table Employee. The column(s) of the Employee table [ID, EMPLOYEE_NAME, EMPLOYEE_EMAIL, ADDRESS, CITY, and COUNTRY]:

Stored Procedure Example

CREATE PROCEDURE SelectAllEmployees
AS
SELECT * FROM Employee
GO;

Execute Stored Procedure Example

EXEC SelectAllEmployee;

Stored Procedure With One Parameter

Just list One parameter and the data type separated by a comma in One Parameter Stored Procedure.

Create Stored Procedure With One Parameter

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

Execute Stored Procedure With One Parameter

EXEC SelectAllEmployees CITY = "Seoul";

Stored Procedure with Multiple Parameters

Stored Procedure with Multiple Parameters, Just list each parameter and the data type separated by a comma only.

Create Stored Procedure With Multiple Parameter

CREATE PROCEDURE SelectAllEmployees @CITY nvarchar(30), @COUNTRY nvarchar(30)
AS
SELECT * FROM Employee
WHERE CITY = @CITY AND COUNTRY = @COUNTRY
GO;

Execute Stored Procedure With Multiple Parameter

EXEC SelectAllEmployees CITY = "Seoul", COUNTRY ="South Korea";