Home > DeveloperSection > Articles > Stored Procedure in SQL Server

Stored Procedure in SQL Server


Database Database 
Ratings:
0 Comment(s)
 3229  View(s)
Rate this:

Stored Procedure in SQL Server

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)

as

begin

      Select Id, Name,State from 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

Stored Procedure in SQL Server

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

as

begin

      Select * from StudentDetail

end  

To execute stored procedure again we write simple statement:

Execute|exec Pr1

Output

Stored Procedure in SQL Server

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

Drop Procedure Pr1


Don't want to miss updates? Please click the below button!

Follow MindStick