Modify a Stored Procedure

This topic describes how to modify a stored procedure in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

• Before you begin:  Limitations and Restrictions, Security

• To alter a procedure, using:  SQL Server Management Studio, Transact-SQL

Before You Begin

Limitations and Restrictions

[Top]

Transact-SQL 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

[Top]

Requires ALTER PROCEDURE permission on the procedure.

How to Modify a Stored Procedure

You can use one of the following:

• SQL Server Management Studio

• Transact-SQL

Using SQL Server Management Studio

To modify a procedure in Management Studio

1. In Object Explorer, connect to an instance of Database Engine 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 Transact-SQL script, on the File menu, click Save As. Accept the file name or replace it with a new name, and then click Save.

Security Note
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 Database Engine 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 AdventureWorks2012 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 Adventure Works Cycles database, the products they supply, their credit ratings, and their availability.

   USE AdventureWorks2012;
   GO
   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.

   USE AdventureWorks2012;
   GO
   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 Transact-SQL 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
   

[Top]

See Also

Reference

ALTER PROCEDURE (Transact-SQL)