Geek Answers Handbook

Replace the stored procedure if the condition is met

I want to change the stored procedure if the condition exists. I want to leave the stored procedure as is if the condition is not met, so drop / create is actually not an option.

Trying to put the contents of ALTER PROC inside an IF block causes errors for me. Any thoughts?

+6
sql-server tsql stored-procedures
source share
2 answers
IF (condition) EXEC ('ALTER PROC ...')

ALTER / CREATE PROC should be the first in the package, so this is the only way. If you do not

 IF NOT (condition) RAISERROR('abort connection with high severity', 20, 1) GO ALTER PROC ... GO
+6
source share

You can use the NOEXEC setting for this, but be careful. When you turn on NOEXEC ON, you should turn it back to the OFF position at the end of your script.

 IF (CONDITION) SET NOEXEC OFF --Enables execution of code (Default) ELSE SET NOEXEC ON --Disables execution of code GO ALTER PROCEDURE MYPROC AS --STATEMENTS GO --END OF ALTER BLOCK SET NOEXEC OFF --RESTORES NOEXEC SETTING TO ITS DEFAULT
+4
source share

More articles:


All Articles