如果满足条件，请更改存储过程

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.

如果存在条件，我希望更改存储过程。如果条件不满足，我想保留存储过程，因此drop / create实际上不是一个选项。

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

试图将ALTER PROC的内容放在IF块中会给我带来错误。有什么想法吗？

2 个解决方案

#1

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

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

ALTER / CREATE PROC必须是批处理中的第一个，所以这是唯一的方法。除非你这样做

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

GO

#2

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.

您可以使用NOEXEC设置来执行此操作，但要小心。一旦打开NOEXEC，就必须在脚本结束时将其恢复为OFF。

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

#1

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