---
title: "Stored Procedure in SQL Server"  
description: "A Stored Procedure is a precompiled object stored in the database. Stored procedures can invoke the Data Definition Language (DDL) and Data Manipulati"  
author: "Sachindra Singh"  
published: 2011-02-14  
updated: 2020-03-04  
canonical: https://www.mindstick.com/articles/453/stored-procedure-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# 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)asbegin      Select Id, Name,State from StudentDetail where City=@Cityend  
```

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](https://www.mindstick.com/mindstickarticle/6c354c65-e441-432c-88a3-f693b0b77956/images/203d6640-31f0-483b-87f9-a3d7c7659894.png)

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 asbegin      Select * from StudentDetailend  
```

To execute stored procedure again we write simple statement:

Execute|exec Pr1

##### Output

![Stored Procedure in SQL Server](https://www.mindstick.com/mindstickarticle/6c354c65-e441-432c-88a3-f693b0b77956/images/21e9950a-c0ac-4913-9105-9c111a51a48b.png)

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

Drop Procedure Pr1

\

---

Original Source: https://www.mindstick.com/articles/453/stored-procedure-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.