Modify a stored procedure
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This article describes how to modify a stored procedure in SQL Server by using SQL Server Management Studio or Transact-SQL.
Limitations
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.
Permissions
Requires ALTER PROCEDURE permission on the procedure.
Use SQL Server Management Studio
To modify a procedure in SQL Server Management Studio:
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
Expand Stored Procedures, right-click the procedure to modify, and then select Modify.
Modify the text of the stored procedure.
To test the syntax, on the Query menu, select Parse.
To save the modifications to the procedure definition, on the Query menu, select Execute.
To save the updated procedure definition as a Transact-SQL script, on the File menu, select Save As. Accept the file name or replace it with a new name, and then select Save.
Important
Validate all user input. Do not concatenate user input before you validate it. Never execute a command constructed from unvalidated user input. Unvalidated user input makes your database vulnerable a type of exploit called a SQL injection attack. For more information, see SQL injection.
Use Transact-SQL
To modify a procedure using T-SQL commands:
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
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
AdventureWorks2022
database.On the File menu, select New Query.
Copy and paste the following example into the query editor. The example creates the
Purchasing.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.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
On the File menu, select New Query.
Copy and paste the following example into the query editor. The example modifies the
uspVendorAllInfo
procedure. TheEXECUTE AS CALLER
clause is removed and the body of the procedure is modified to return only those vendors that supply the specified product. TheLEFT
andCASE
functions customize the appearance of the result set.Important
Dropping and recreating an existing stored procedure removes permissions that have been explicitly granted to the stored procedure. Use
ALTER
to modify the existing stored procedure instead.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
To save the modifications to the procedure definition, on the Query menu, select Execute.
To save the updated procedure definition as a Transact-SQL script, on the File menu, select Save As. Accept the file name or replace it with a new name, and then select Save.
To run the modified stored procedure, execute the following example.
EXEC Purchasing.uspVendorAllInfo N'LL Crankarm'; GO
Related content
Feedback
https://aka.ms/ContentUserFeedback.
În curând: Pe parcursul anului 2024, vom elimina treptat Probleme legate de GitHub ca mecanism de feedback pentru conținut și îl vom înlocui cu un nou sistem de feedback. Pentru mai multe informații, consultați:Trimiteți și vizualizați feedback pentru