How to Create Stored Procedure in SQL
In this article I am going to explain about how to create stored procedure.
Introduction
CREATE PROC statement is used to create a stored procedure. During creating a stored procedure we need to remember that stored procedure names can't be same as the name of any other object of database.
Here I am going to explain some basic terms of stored procedure
- CREATE PROC Statement is used to create a stored procedure.
- CREATE PROC Statement must be first statement in batch.
- We can create temporary stored procedure in SQL server. In temporary stored procedure we use # sign as prefix with name in local stored procedure and ## sign as prefix with name of global stored procedure.
- You can pass one or more parameter in stored procedure from calling program of stored procedure.
- WITH RECOMPILE Statement restrict the stored procedure to be recompiled every time when it is used in application. It reduces system performance so avoid it.
- WITH ENCRYPTION Statement in Stored prevents users to view the code of stored procedure.
A Statement that create vendors table
Statements that insert data in vendors table
A Statement that is used to fetch data from vendors table
A Statement that creates a Stored Procedure
A Statement that run the Stored Procedure
A Statement that fetch data from vendorscopy table created by stored procedure