WHAT IS A STORED PROCEDURE

STORED PROCEDURE in SQL

Stored Procedure in SQL is a group of SQL Statements stored in the database for performing a particular task.

Syntax of stored procedure in SQL-

CREATE PROCEDURE <Procedure_Name> AS

<SQL_statements>

GO;

Syntax to execute stored procedure in SQL-

EXEC Procedure_Name;

Syntax of Stored Procedure with Parameter in SQL-

CREATE PROCEDURE <Procedure_Name> @<Parameter_Name1> <datatype (size)>…….. @<Parameter_NameN> <datatype (size)> AS

<SQL_statements>

GO;

Syntax to execute Stored Procedure in SQL-

EXEC Procedure_Name

@Parameter_Name1=Value1,…

@Parameter_NameN=ValueN;

Example of Stored Procedure in SQL-

CREATE PROCEDURE Allcustomers @City NVARCHAR(50), @PostalCode NVARCHAR(50) AS

SELECT *

FROM Customers

WHERE City=@City AND  PostalCode = @PostalCode 

GO;

EXEC Allcustomers @City=‘London’ , @PostalCode=‘WA1 1DP’;


Leave a Reply

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