SQL Server FAQ - "ALTER PROCEDURE" - Modifying Existing Stored Procedures

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Modify an Existing Stored Procedure?

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)

(Continued on next topic...)