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.

Stored Procedure in SQL - Syntax and Example

Stored Procedure in SQL – Syntax and Example

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

CustomerIDCustomerNameContactNameAddressCityPostal codeCountry
1RamSita 78 sudama nagarIndore452001India
2SamRose Avda. de la Constitución 2222México D.F.05021Mexico
3AntonioMorenoMataderos 2312Mexico D.F.05023Mexico
4Wendy Thomas120 Hanover Sq.LondonWA1DPUK
5BerglundsChristinaBerguvsvägen 8LuleaS- 95822Sweden

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

1 Response

  1. Amit Rawat says:

    How many DDL and DML queries we can write in one Stored Procedure ?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.