CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Matt
Matt

Reputation: 2098

Alter stored procedure if condition is met

I am looking to alter a stored procedure if a condition exists. I want to leave the stored procedure as is if the condition is not met, so drop/create is not really an option.

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

Upvotes: 8

Views: 4709

Answers (2)

Jairo Ramírez
Jairo Ramírez

Reputation: 41

You can use the NOEXEC setting to do it, but be careful. Once you turn NOEXEC ON, you must turn it back to OFF 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

Upvotes: 4

gbn
gbn

Reputation: 432271

IF (condition)
  EXEC ('ALTER PROC ...')

ALTER/CREATE PROC must be first in the batch so this is the only way. Unless you do this

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

GO

Upvotes: 6

Related Questions