---
title: "Stored Procedure in Microsoft SQL Server"  
description: "Stored procedures assist   in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a com"  
author: "Anonymous User"  
published: 2010-07-18  
updated: 2020-03-04  
canonical: https://www.mindstick.com/articles/36/stored-procedure-in-microsoft-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Stored Procedure in Microsoft SQL Server

Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure.

##### Syntax to create Stored Procedure

CREATE PROCEDURE ProcedureName

AS

Body of the Procedure

##### Example

```
create procedure sp1asbegin      select * from Doctorsend
```

Now, once the procedure is created we need to execute procedure.

##### Syntax to execute procedure

EXECUTE ProcedureName

Or

EXEC ProcedureName

##### Example

EXECUTE sp1

![Stored Procedure in Microsoft SQL Server](https://www.mindstick.com/mindstickarticle/3db9ca2c-0a41-4f7a-9ce9-82f743563737/images/d0e53d83-f20c-4d99-a0d4-530e3f76b1ea.png)

##### Syntax for modifying procedure

ALTER PROCEDURE ProcedureName

AS

Body of Procedure

**Example**

```
ALTER PROCEDURE sp1ASBEGIN      SELECT * FROM Doctors WHERE ID>4END
```

##### Syntax for deleting procedure

DROP PROCEDURE ProcedureName

##### Example

DROP PROCEDURE sp1

##### Syntax for passing arguments to procedure

CREATE PROCEDURE ProcedureName

@ParameterName DataType

AS

Body of the Procedure

##### Example

```
CREATE PROCEDURE SP2@IDNo INTASBEGIN      SELECT * FROM Doctors where ID=@IDNoEND
```

##### Executing parameterize procedure

EXECUTE sp25

![Stored Procedure in Microsoft SQL Server](https://www.mindstick.com/mindstickarticle/3db9ca2c-0a41-4f7a-9ce9-82f743563737/images/d53642cd-dc42-4a68-b807-e7f62c9aa130.png)

##### \

##### Benefits of Stored Procedures

- Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
- Reduced client/server traffic.If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
- Efficient reuse of code and programming abstraction.Stored procedures can be used by multiple users and client programs.
- Enhanced security controls.You can grant users permission to execute a stored procedure independently of underlying table permissions.

##### Difference between Function and Stored Procedure

- A function is a subprogram written to perform certain computations and return a single value.
- Functions must return a value (using the RETURN keyword), but for stored procedures this is not compulsory.
- Stored procedures can use RETURN keyword but without any value being passed.
- Functions could be used in SELECT statements, provided they don’t do any data manipulation. However, procedures cannot be included In SELECT statements.
- A function can have only IN parameters, while stored procedures may have OUT or INOUT parameters.
- A stored procedure can return multiple values using the OUT parameter or return no value at all.

---

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

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