TRENDING TOPICS

×

SQL STORED PROCEDURES

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]:

ID EMPLOYEE_NAME EMPLOYEE_EMAIL ADDRESS CITY COUNTRY
1 Mark J. mark@gmail.com House 53, Sq. London U.K.
2 Lack Josh lackjosh@gmail.com 2745 road mark Delhi India
3 Kim Juhu kimjuhu@gmail.com Street 587327, 34 House Seoul South Korea
4 Jack Jenifer jenifer@gmail.com 464673 Mataderos Mexico D.F. Mexico
5 McKinley mckinley@gmail.com 54, Road Washington Washington U.S.
6 Alena aln@gmail.com House No. 6563 Tokyo Japan Japan
7 Mack Marina mmk@gmail.com 43 Road, NH San Francisco U.S.
8 Dipak Jha jha@gmail.com Mahanager, 26 Road Lucknow India

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";

Advertise With Us

We offer sponsored posts and guest posts with do-follow links, back-links, and other advertising services, To advertise email us at "advertise@gdatamart.com"

© 2021 GDATAMART.COM (All Rights Reserved)