Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
procedure

What is an SQL procedure?

Educative Answers Team

An SQL procedure, also called a stored procedure, is a prepared SQL code that you can save so that it ​can be reused over and over again. It is very similar to the concept of functions in programming languages like C++ and Python.

If you have an SQL query that you write over and over again, save it as a stored procedure. That way, all you have to do to execute it is call it.

svg viewer

Syntax


--------CREATING A PROCEDURE----------

CREATE PROCEDURE procedure_name
AS
sql_statements
GO;

--------CREATING A PROCEDURE WITH PARAMETERS----------

CREATE PROCEDURE procedure_name @attribute type(size), ...
AS
sql_statements
GO;

--------EXECUTING A PROCEDURE----------

EXEC procedure_name

Examples

Consider the following employees table.

id name dept age
1 John Intern 25
2 David Intern 30
3 Mike Engineer 29
4 Alex HR 27
5 Jake Intern 25

Creating a procedure to get the information of all employees


--------CREATING THE PROCEDURE----------

CREATE PROCEDURE GetAllEmployees
AS
SELECT * FROM Customers
GO;

--------EXECUTING THE PROCEDURE----------

EXEC GetAllEmployees;

Creating a procedure to get the information of all interns


--------CREATING THE PROCEDURE----------

CREATE PROCEDURE GetAllInterns @dept nvarchar(30)
AS
SELECT * FROM Customers WHERE dept = @dept
GO;

--------EXECUTING THE PROCEDURE----------

EXEC GetAllInterns dept = "Intern";

Creating a procedure to get the information of all interns above the age of 27


--------CREATING THE PROCEDURE----------

CREATE PROCEDURE GetAllInternsAbove @dept nvarchar(30), @age nvchar(10)
AS
SELECT * FROM Customers WHERE dept = @dept AND age > @age
GO;

--------EXECUTING THE PROCEDURE----------

EXEC GetAllInternsAbove dept = "Intern", age = 27;

RELATED TAGS

sql
procedure
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring