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;
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
How many DDL and DML queries we can write in one Stored Procedure ?