Stored Procedure in SQL – Syntax and Example

1. Objective – Stored Procedure in SQL

In our last SQL tutorial, we discussed COUNT function in SQL. Today, we will see the Stored Procedure in SQL Server. Moreover, we will discuss SQL Stored Procedure example. Also, we will look at how to save and create Stored Procedure in SQL. 

So, let us start the SQL Stored Procedure tutorial.

2. What is the SQL Stored Procedure?

A stored procedure in SQL is a type of code in SQL that can be stored for later use and can be used many times. So, whenever you need to execute the query, instead of calling it you can just call the stored procedure. Values can be passed through stored procedures.
The basic role of comments in SQL is to explain SQL statements and also to prevent the execution of some statements.
Let’s revise SQL Create Database

3. Stored Procedure in SQL

The Syntax of SQL Stored Procedure–

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

a. Execute a Stored Procedure in SQL

EXEC procedure_name;

• Example of SQL Stored Procedure Execution-

Have a look at SQL Constraint
Below is given the database

CustomerID	CustomerName	ContactName	Address	City	Postal code	Country
1	Ram	Sita	78 sudama nagar	Indore	452001	India
2	Sam	Rose	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio	Moreno	Mataderos 2312	Mexico D.F.	05023	Mexico
4	Wendy	Thomas	120 Hanover Sq.	London	WA1DP	UK
5	Berglunds	Christina	Berguvsvägen 8	Lulea	S- 95822	Sweden

Example of Stored Procedure in SQL
The following statements create a stored procedure and help select all records

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Execution of Stored Procedures in SQL–

EXEC SelectAllCustomers;

Do you know about SQL Index

b. Stored Procedure with One Parameter

• Example of SQL Stored Procedure with one parameter- 

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

Execution of Stored Procedures in SQL –

EXEC SelectAllCustomers City = "London";

c. Stored Procedure with Multiple Parameters

Setting up multiple parameters is very easy, we just need to list them and separate them using a comma.

• Example of SQL Stored Procedure with Multiple Parameters

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Execution of Stored Procedures in SQL 

EXEC SelectAllCustomers City = "London", PostalCode = "WA1 1DP";

Let’s revise SQL Null Values
So, this was all in Stored Procedure in SQL. Hope you like our explanation.

4. Conclusion – Stored Procedure in SQL

Hence, in this SQL Stored Procedure tutorial, we discussed the meaning of Stored Procedure in SQL. Moreover, we learned SQL Stored procedure example. Also, we saw how to create and save Stored Procedure in SQL. Still, if you have any query, ask in the comment tab.
See also –
SQL Query Optimization
For reference