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)
⇒ Creating Stored Procedures with Parameters in SQL Server
⇐ sys.sql_modules - Getting Stored Procedure Definitions Back in SQL Server
2017-01-05, 3316🔥, 0💬
Popular Posts:
How To Start MySQL Server in MySQL? If you want to start the MySQL server, you can run the "mysqld" ...
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...
How To Convert Numeric Expression Data Types using the CAST() Function in SQL Server Transact-SQL? I...
How to download and install Microsoft .NET Framework Version 2.0 in SQL Server? .NET Framework Versi...
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...