Benefits of Store Procedure

 

                       A Stored Procedure is a type of code in SQL that can be stored for later use and can be used many times. Stored procedures are like fundamentally SQL query. So, whenever you need to execute the query, in place of calling it you can direct call the stored procedure. You can also pass parameters to a stored procedure, so that the stored procedure can works based on the parameter values that is passed. There are several reasons, but the main one is security. Stored procedures are parameterized, so they are less pregnable to attacks. here i am explain about Store Procedure with example.  

Stored Procedure Syntax or code

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure

EXEC procedure_name;

Demo Database

Table named Grahak with column and rows with Values

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Ram	9871234563	#47 Sector 22	Chandigah	160002	Japan
2	Sham	9876543216	#4110 Sector 122	Mohali	140002	India
3	Shivam	3278955152	#5 Sector 88	Gurgaon	180002	UAE
4	Ravi	2277894562	#5 Sector 48	Badlapur	220002	North korea

Stored Procedure Example

Below mentioned SQL code creates a stored procedure named " complete_Grahaks " shich selects all data from the " Grahak " table:

Example

CREATE PROCEDURE complete_Grahaks
AS
SELECT * FROM Grahak
GO;

Execute the stored procedure above as follows:

Example

EXEC complete_Grahaks;

Stored Procedure With One Parameter

The following SQL statement creates a stored procedure that selects Customers from a particular City from the " Grahak " table:

Example

CREATE PROCEDURE complete_Grahaks @City nvarchar(30)
AS
SELECT * FROM Grahak WHERE City = @City
GO;

Execute the stored procedure above as follows:

Example

EXEC complete_Grahaks @City = 'India';

Stored Procedure With Multiple Parameters

Creating store procedure with multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.

The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Grahak" table:

Example

CREATE PROCEDURE complete_Grahaks @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Grahak WHERE City = @City AND PostalCode = @PostalCode
GO;

Execute the stored procedure above as follows:

EXEC complete_Grahaks @City = 'Chandigarh', @PostalCode = '160002'; 

 

 Follow the Asp.Net channel on WhatsApp: https://whatsapp.com/channel/0029VbAovoCBA1f6Fnn2kU3X