What are stored procedures in SQL?

What is SQL?

Structured Query Language (SQL) is a language that is used to access and manipulate databases. With the help of SQL queries, we can store data in the database in an accessible form, easily retrieve it, and update it whenever needed. Some basic and common SQL commands are INSERT, SELECT, UPDATE, DELETE, WHERE, etc.

Similarly to functions that we make so that code can be used again, we have stored procedures in SQL.

Stored procedures

In SQL, a stored procedure is a prepared SQL code that developers can save so that the code can be easily reused later.

If we have SQL code that needs to be used many times, we can store it as stored procedures and call it whenever necessary. We can also pass parameters to the stored procedures, just like in functions, to make it more customizable.

Syntax

CREATE PROCEDURE SQL_procedure_name
AS
SQL_statements
GO;

The code above shows us how to create a stored procedure. To create a stored procedure, you can use the CREATE PROCEDURE statement followed by the stored procedure name. Then, after the AS statement, we write all the SQL queries/code that needs to be reused and conclude the stored procedure with the GO statement. Below is the code to call/execute the stored procedure.

EXEC SQL_procedure_name;

Example

Consider the following database named information.

CREATE TABLE information (
-- <Variable name> <Variable type>
Name varchar(255),
Age varchar(255),
Gender varchar(255),
City varchar(255)
);
INSERT INTO
information
(Name, Age, Gender , City)
VALUES
-- <Variable values>
('George','20', 'Male', 'London'),
('Emma','22', 'Female', 'Manchester'),
('Harry','15', 'Male', 'Cambridge'),
('Ava','17', 'Female', 'Manchester'),
('Olivia','25', 'Female', 'Manchester'),
('Thomas','33', 'Male', 'Cambridge');
SELECT * FROM information

Now, let’s create a stored procedure with the code to select all entries from the database above.

CREATE PROCEDURE show_info
AS
BEGIN
SELECT
Name,
Gender
FROM
information
END;
EXEC show_info

The following result will be displayed.

Name Gender
George Male
Emma Female
Harry Male
Ava Female
Olivia Female
Thomas Male

Parameters in stored procedures

As mentioned earlier, we can also pass parameters in stored procedures.

Consider an example of a stored procedure with a single parameter.

CREATE PROCEDURE showFemale @gender nvarchar(30)
AS
SELECT * FROM information WHERE Gender= @gender
GO;
EXEC showFemale @gender= 'Female'

The output of the code above will be as follows.

Name Age Gender City
Emma 22 Female Manchester
Ava 17 Female Manchester
Olivia 25 Female Manchester

We can also create stored procedures with multiple parameters, as shown below.

CREATE PROCEDURE filter @gender nvarchar(30) @age nvarchar(10)
AS
SELECT * FROM information WHERE Gender= @gender AND Age >= @age
GO;
EXEC filter @gender='Male', @age = 22

The code above will display all the male entries with an age greater than or equal to 22.

Free Resources