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 GlobalGuideLineDatabase;

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.