如何有条件地在 SQL Server 中创建存储过程? [英] How do I conditionally create a stored procedure in SQL Server?

问题描述

作为我的集成策略的一部分，我运行了一些 SQL 脚本来更新数据库.所有这些脚本所做的第一件事是检查它们是否需要运行，例如:

if @version <>@期望开始声明@error varchar(100);set @error = '版本无效.您的版本是 ' + convert(varchar, @version) + '.此脚本需要版本 ' + convert(varchar, @expects) + '.';raiserror(@error, 10, 1);结尾别的开始...这里的sql语句...结尾

效果很好！除非我需要添加一个存储过程.create proc"命令必须是一批 sql 命令中的唯一命令.在我的 IF 语句中放入create proc"会导致此错误:

<前>'CREATE/ALTER PROCEDURE' 必须是查询批处理中的第一条语句.

哎哟！如何将 CREATE PROC 命令放入我的脚本中，并且仅在需要时才执行?

解决方案

这是我的想法:

将其包裹在 EXEC() 中，如下所示:

if @version <>@期望开始...剪...结尾别的开始exec('CREATE PROC MyProc AS SELECT ''Victory!''');结尾

就像一个魅力！

As part of my integration strategy, I have a few SQL scripts that run in order to update the database. The first thing all of these scripts do is check to see if they need to run, e.g.:

if @version <> @expects
    begin
        declare @error varchar(100);
        set @error = 'Invalid version. Your version is ' + convert(varchar, @version) + '. This script expects version ' + convert(varchar, @expects) + '.';
        raiserror(@error, 10, 1);
    end
else
    begin
        ...sql statements here...
    end

Works great! Except if I need to add a stored procedure. The "create proc" command must be the only command in a batch of sql commands. Putting a "create proc" in my IF statement causes this error:

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Ouch! How do I put the CREATE PROC command in my script, and have it only execute if it needs to?

解决方案

Here's what I came up with:

Wrap it in an EXEC(), like so:

if @version <> @expects
    begin
        ...snip...
    end
else
    begin
        exec('CREATE PROC MyProc AS SELECT ''Victory!''');
    end

Works like a charm!

这篇关于如何有条件地在 SQL Server 中创建存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！