A Stored Procedure is a precompiled object stored in the database. Stored procedures can invoke the Data Definition Language (DDL) and Data Manipulation Language (DML) statements and can return values. Stored procedures are special objects available in SQL Server. It is a precompiled statement where all the preliminary parsing operations are performed and the statements are ready for execution.
It is very fast when compared to ordinary SQL statements where the SQL statements will undergo a sequence of steps to fetch the data. When the Create Procedure statement is executed, the server compiles the procedure and saves it is as a database object. The procedure is then available for various applications to execute. After creating a stored procedure, you can execute the procedure. You can also alter the procedure definition or drop it, if not required.
Following example create a stored procedure and fetch data from StudentDetail table.
Create Procedure Pr1 @City varchar(20)
Select Id, Name,State from StudentDetail where City=@City
And when we want to execute Stored Procedure just write simple statement as shown below:
“Pr1”is Stored Procedure name
In the output you will get that student detail that belongs to “Allahabad” city.
A stored procedure can be modified by using the “Alter Procedure “statement .The statement syntax of the Alter Procedure statement is:
Alter Procedure Pr1
Select * from StudentDetail
To execute stored procedure again we write simple statement:
We can drop Stored Procedure from the database by using “Drop procedure” statement. The syntax of the Drop Procedure statement is:
Drop Procedure Pr1