Collections:
"ALTER PROCEDURE" - Modifying Existing Stored Procedures in SQL Server
How To Modify an Existing Stored Procedure in SQL Server Transact-SQL?
✍: FYIcenter.com
If you find a mistake in an existing stored procedure previously created, you can drop (delete) it and create it again correctly. But dropping a stored procedure may affect other database objects who are depending on this stored procedure.
So the best way to correct a mistake in an existing stored procedure is to use the "ALTER PROCEDURE" statement as shown in the following tutorial example:
USE FyiCenterData; GO -- Finding a mistake - the last line is wrong SELECT m.definition FROM sys.sql_modules m, sys.procedures p WHERE m.object_id = p.object_id AND p.name = 'ShowFaq'; GO definition ----------------------------------------- CREATE PROCEDURE ShowFaq AS BEGIN PRINT 'Number of questions:'; SELECT COUNT(*) FROM Faq; PRINT 'First 5 questions:' SELECT TOP 5 * FROM Faq; END; CREATE TABLE Faq (Question VARCHAR(80)); (1 row(s) affected) -- Modifying the stored procedure ALTER PROCEDURE ShowFaq AS BEGIN PRINT 'Number of questions:'; SELECT COUNT(*) FROM Faq; PRINT 'First 5 questions:' SELECT TOP 5 * FROM Faq; END; GO Command(s) completed successfully. -- Confirmation of the correction SELECT m.definition FROM sys.sql_modules m, sys.procedures p WHERE m.object_id = p.object_id AND p.name = 'ShowFaq'; GO definition ----------------------------------------- CREATE PROCEDURE ShowFaq AS BEGIN PRINT 'Number of questions:'; SELECT COUNT(*) FROM Faq; PRINT 'First 5 questions:' SELECT TOP 5 * FROM Faq; END; (1 row(s) affected)
Â
2017-01-05, 1234👍, 0💬
Popular Posts:
How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initializat...
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL...
How To Create a View on an Existing Table in SQL Server? If you want to a view on an existing table,...
What is Microsoft SQL Server in SQL Server? Microsoft SQL Server is a relational database management...
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...