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, 1378👍, 0💬
Popular Posts:
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...
What Happens to Your Transactions When ERROR 1205 Occurred in MySQL? If your transaction receives th...
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...