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.

CreateProcedure Pr1 @City  varchar(20)
as
begin
      Select Id, Name,Statefrom StudentDetail where City=@City
end   

And when we want to execute Stored Procedure just write simple statement as shown below:

Execute|exec Pr1'Allahabad'

“Pr1”is Stored Procedure name

Output

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:

AlterProcedure Pr1 
as
begin
      Select*from StudentDetail
end   

To execute stored procedure again we write simple statement:

Execute|exec Pr1

Output

We can drop Stored Procedure from the database by using “Drop procedure” statement. The syntax of the Drop Procedure statement is:

DropProcedure Pr1