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, 1700🔥, 0💬
Popular Posts:
Where to find tutorials to answer some frequently asked questions on Microsoft SQL Server Transact-S...
Where to find answers to frequently asked questions I am new to Oracle database. Here is a list of f...
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...
How AdventureWorksLT tables are related in SQL Server? There are 12 user tables defined in Adventure...
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...