Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
communitycreator

What are stored procedures in SQL?

Hammad Qayyum

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;
SQL version 13.0

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;
SQL version 13.0

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 
SQL version 13.0

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;
SQL version 13.0
EXEC show_info
SQL version 13.0

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;
SQL version 13.0
EXEC showFemale @gender= 'Female'
SQL version 13.0

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;
SQL version 13.0
EXEC filter @gender='Male', @age = 22
SQL version 13.0

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

RELATED TAGS

sql
communitycreator
RELATED COURSES

View all Courses

Keep Exploring