---
title: "Modify a Stored Procedure | Microsoft Docs"
ms.custom: ""
ms.date: "03/14/2017"
ms.prod: sql
ms.reviewer: ""
ms.technology: stored-procedures
ms.topic: conceptual
helpviewer_keywords:
- "modifying stored procedures"
- "editing stored procedures"
- "stored procedures [SQL Server], modifying"
ms.assetid: 13396239-6100-48ce-aa34-461358d99c92
author: "stevestein"
ms.author: "sstein"
monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current"
---
# Modify a Stored Procedure
[!INCLUDE[appliesto-ss-asdb-asdw-pdw-md](../../includes/appliesto-ss-asdb-asdw-pdw-md.md)]
## This topic describes how to modify a stored procedure in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] by using [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] or [!INCLUDE[tsql](../../includes/tsql-md.md)].
- **Before you begin:** [Limitations and Restrictions](#Restrictions), [Security](#Security)
- **To alter a procedure, using:** [SQL Server Management Studio](#SSMSProcedure), [Transact-SQL](#TsqlProcedure)
## Before You Begin
### Limitations and Restrictions
[!INCLUDE[tsql](../../includes/tsql-md.md)] stored procedures cannot be modified to be CLR stored procedures and vice versa.
If the previous procedure definition was created using WITH ENCRYPTION or WITH RECOMPILE, these options are enabled only if they are included in the ALTER PROCEDURE statement.
### Security
#### Permissions
Requires ALTER PROCEDURE permission on the procedure.
## How to Modify a Stored Procedure
You can use one of the following:
- [SQL Server Management Studio](#SSMSProcedure)
- [Transact-SQL](#TsqlProcedure)
### Using SQL Server Management Studio
**To modify a procedure in Management Studio**
1. In Object Explorer, connect to an instance of [!INCLUDE[ssDE](../../includes/ssde-md.md)] and then expand that instance.
2. Expand **Databases**, expand the database in which the procedure belongs, and then expand **Programmability**.
3. Expand **Stored Procedures**, right-click the procedure to modify, and then click **Modify**.
4. Modify the text of the stored procedure.
5. To test the syntax, on the **Query** menu, click **Parse**.
6. To save the modifications to the procedure definition, on the **Query** menu, click **Execute**.
7. To save the updated procedure definition as a [!INCLUDE[tsql](../../includes/tsql-md.md)] script, on the **File** menu, click **Save As**. Accept the file name or replace it with a new name, and then click **Save**.
> [!IMPORTANT]
> Validate all user input. Do not concatenate user input before you validate it. Never execute a command constructed from unvalidated user input.
### Using Transact-SQL
**To modify a procedure in Query Editor**
1. In **Object Explorer**, connect to an instance of [!INCLUDE[ssDE](../../includes/ssde-md.md)] and then expand that instance.
2. Expand **Databases**, expand the database in which the procedure belongs. Or, from the tool bar, select the database from the list of available databases. For this example, select the [!INCLUDE[ssSampleDBobject](../../includes/sssampledbobject-md.md)] database.
3. On the **File** menu, click **New Query**.
4. Copy and paste the following example into the query editor. The example creates the `uspVendorAllInfo` procedure, which returns the names of all the vendors in the [!INCLUDE[ssSampleDBCoFull](../../includes/sssampledbcofull-md.md)] database, the products they supply, their credit ratings, and their availability.
```
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL
DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON;
SELECT v.Name AS Vendor, p.Name AS 'Product name',
v.CreditRating AS 'Rating',
v.ActiveFlag AS Availability
FROM Purchasing.Vendor v
INNER JOIN Purchasing.ProductVendor pv
ON v.BusinessEntityID = pv.BusinessEntityID
INNER JOIN Production.Product p
ON pv.ProductID = p.ProductID
ORDER BY v.Name ASC;
GO
```
5. On the **File** menu, click **New Query**.
6. Copy and paste the following example into the query editor. The example modifies the `uspVendorAllInfo` procedure. The EXECUTE AS CALLER clause is removed and the body of the procedure is modified to return only those vendors that supply the specified product. The `LEFT` and `CASE` functions customize the appearance of the result set.
```
ALTER PROCEDURE Purchasing.uspVendorAllInfo
@Product varchar(25)
AS
SET NOCOUNT ON;
SELECT LEFT(v.Name, 25) AS Vendor, LEFT(p.Name, 25) AS 'Product name',
'Rating' = CASE v.CreditRating
WHEN 1 THEN 'Superior'
WHEN 2 THEN 'Excellent'
WHEN 3 THEN 'Above average'
WHEN 4 THEN 'Average'
WHEN 5 THEN 'Below average'
ELSE 'No rating'
END
, Availability = CASE v.ActiveFlag
WHEN 1 THEN 'Yes'
ELSE 'No'
END
FROM Purchasing.Vendor AS v
INNER JOIN Purchasing.ProductVendor AS pv
ON v.BusinessEntityID = pv.BusinessEntityID
INNER JOIN Production.Product AS p
ON pv.ProductID = p.ProductID
WHERE p.Name LIKE @Product
ORDER BY v.Name ASC;
GO
```
7. To save the modifications to the procedure definition, on the **Query** menu, click **Execute**.
8. To save the updated procedure definition as a [!INCLUDE[tsql](../../includes/tsql-md.md)] script, on the **File** menu, click **Save As**. Accept the file name or replace it with a new name, and then click **Save**.
9. To run the modified stored procedure, execute the following example.
```
EXEC Purchasing.uspVendorAllInfo N'LL Crankarm';
GO
```
## See Also
[ALTER PROCEDURE (Transact-SQL)](../../t-sql/statements/alter-procedure-transact-sql.md)