Stored Procedure in SQL – Syntax and Example

Keeping you updated with latest technology trends, Join DataFlair on Telegram

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

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

3 Responses

  1. Amit Rawat says:

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

  2. Sujith says:

    Hi how to execute SP with multiple values for single parameter. In the above example suppose for city I want give two city names .

  3. Sujith says:

    Also while executing above procedure is not working. but when I put @ symbol before parameter then its working.

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.