TSQL - 在事务语句中创建一个存储过程 [英] TSQL - create a stored proc inside a transaction statement

问题描述

我有一个 sql 脚本，它被设置为投入生产.我已将各种项目包装到单独的事务中.在每个事务中，我们都创建了存储过程.我收到错误消息

I have a sql script that is set to roll to production. I've wrapped the various projects into separate transactions. In each of the transactions we created stored procedures. I'm getting error messages

消息 156，级别 15，状态 1，第 4 行关键字procedure"附近的语法不正确.

Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'procedure'.

我创建了这个示例脚本来说明

I created this example script to illustrate

Begin Try
Begin Transaction 
    -- do a bunch of add/alter tables here
    -- do a bunch of data manipulation/population here

    -- create a stored proc
  create procedure dbo.test
  as
  begin
    select * from some_table
  end
Commit  
End Try
Begin Catch
    Rollback  
    Declare @Msg nvarchar(max)
    Select @Msg=Error_Message();
    RaisError('Error Occured: %s', 20, 101,@Msg) With Log;
End Catch

该错误似乎暗示我无法在事务内部创建存储过程，但我没有找到任何其他说明的文档(也许 google 今天不友好).

The error seems to imply that I can't create stored procs inside of transaction, but I'm not finding any docs that say otherwise(maybe google isn't being freindly today).

推荐答案

尝试在EXEC('...')中执行create procedure，如下所示:

try doing the create procedure in EXEC('...'), like this:

Begin Try
Begin Transaction 
    -- do a bunch of add/alter tables here
    -- do a bunch of data manipulation/population here

    -- create a stored proc
  EXEC ('create procedure dbo.test
  as
  begin
    select * from some_table
  end')
Commit  
End Try
Begin Catch
    Rollback  
    Declare @Msg nvarchar(max)
    Select @Msg=Error_Message();
    RaisError('Error Occured: %s', 20, 101,@Msg) With Log;
End Catch

GO

这篇关于TSQL - 在事务语句中创建一个存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！