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

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

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