TSQL - 在事务语句中创建存储过程

Question

TSQL - 在事务语句中创建存储过程

sql-serversql-server-2008stored-procedures

11

我有一个 SQL 脚本，已设置为发布生产环境。我将不同的项目封装到不同的事务中。在每个事务中，我们创建了存储过程。我得到了错误消息：

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

我创建了这个示例脚本以说明问题。

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

这个错误似乎意味着我不能在事务中创建存储过程，但我没有找到任何文档表明相反的情况（也许今天谷歌不友好）。

- Chris L

是的，抱歉，我应该提到了。SQL 2008。 - Chris L

我决定将create proc从事务和try/catch中分离出来，放到它自己的部分中。我加入了一个“如果存在则删除”的语句，并在存储过程创建周围添加了一个“如果存在则打印消息”的语句。感谢所有的反馈，我从中学到了一些东西。 - Chris L

4个回答

12

由于有许多语句必须在它们自己的批处理中运行，因此您无法以这种方式编写脚本。相反，我建议采用 Red-Gate 的 SQL Compare 是如何构建其脚本的方法：

Set Xact_Abort On
GO
Begin Transaction 
GO
If object_id('tempdb..#tmpErrors') is not null
    Drop Table #tmpErrors
GO
Create Table #tmpErrors( [Error] int not null )
GO
Create Procedure dbo.Test
As
Begin
    --....
End
GO
If @@Error <> 0 AND @@TranCount >0 Rollback Transaction
GO
IF @@TranCount = 0 Begin Insert #tmpErrors (Error) Select 1 Begin Transaction End
GO

-- more statements

GO
If @@Error <> 0 AND @@TranCount >0 Rollback Transaction
GO
IF @@TranCount = 0 Begin Insert #tmpErrors (Error) Select 1 Begin Transaction End
GO

--.....

IF NOT EXISTS(SELECT * FROM #tmpErrors)
    BEGIN
        PRINT 'The database update succeeded'
        IF @@TRANCOUNT > 0 COMMIT TRANSACTION
    END
ELSE 
    BEGIN
        PRINT 'The database update failed'
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    END
GO

DROP TABLE #tmpErrors
GO

- Thomas

SSMS 抱怨在 #tmpErrors (Error) Select 1 中 Error 不是一个列，它应该是 ID 吗？（或者 Create Table #tmpErrors( Id int not null ) 应该是 Create Table #tmpErrors( Error int not null ) 吗？） - Sam Holder

@SamHolder - 是的。这是我犯的一个笔误，我已经更正了。如果你选择使用 Id，那么创建表语句显然应该使用 Id。如果你选择使用 Error，那么创建表语句就需要根据这个事实进行更新。 - Thomas

感谢澄清。我给你点赞。非常好的答案。 - Sam Holder

这确实是一个非常出色的答案，非常感谢。你从哪里学到的？ - Alex Gordon

@PleaseStopUpvotingMe - 正如我在帖子中提到的那样，这是Red-Gate的SQL Compare脚本其数据更改的方式。 - Thomas

2

KM提出的解决方案存在多种问题:

If the content of what you put in your EXEC() call is incorrect semantically (for example you put a non-existant table in the FROM inside the stored procedure) that error doesn't bubble up and the transaction is not rolled back.
If the content of what you put in your EXEC() call is incorrect syntactically (for example you put SELECTT instead of SELECT inside the stored procedure), the transaction seems to be rolled back but a completely cryptic error bubbles up:
```
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
```

我仍然不知道如何在事务中创建一个过程，同时让事务和try-catch有用。

- user1035920

使用Mile在这里提供的CATCH块：https://dev59.com/MHVC5IYBdhLWcg3wz0h9 - knocte

0

我记得你不能在一个事务中创建、修改或撤销数据库模式对象，包括存储过程（因为这些结构性变化不是事务性的：你无法通过数据更改来回滚它们）。

- Richard

1

我测试了我的答案中的代码，它不仅创建了一个存储过程，而且如果你在代码中强制执行ROLLBACK，它也会回滚。 - KM.

1

@KM：当然，在更近版本的SQL Server中可能已经发生了变化。 - Richard

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- KM. · Accepted Answer

尝试使用 EXEC('...') 在其中执行 create procedure，像这样：

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