使用 Transact-SQL 在 try catch with tran 中更改然后更新的问题 [英] Problem with alter then update in try catch with tran using Transact-SQL

问题描述

这是我尝试通过 sqlcmd (SQL Server 2005) 运行的一些 Transact-SQL.

Here is some Transact-SQL I am trying to run via sqlcmd (SQL Server 2005).

USE PUK;
GO
BEGIN TRANSACTION;
BEGIN TRY

   -- - Modify RETRIEVAL_STAT
   alter table dbo.RETRIEVAL_STAT add
      SOURCE nvarchar(10) NULL,
      ACCOUNTNUMBER nvarchar(50) NULL,
      PUK nvarchar(20) NULL;

   -- transform logic.
   update dbo.RETRIEVAL_STAT set
      SOURCE = 'XX',
      ACCOUNTNUMBER = 'XX',
      PUK = 'XX';

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

我收到以下错误:

(0 rows affected)
Changed database context to 'PUK'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 11
Invalid column name 'SOURCE'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 11
Invalid column name 'ACCOUNTNUMBER'.
Msg 207, Level 16, State 1, Server localhost\SQLEXPRESS, Line 11
Invalid column name 'PUK'.

我猜测这是因为alter语句引入的新列还没有提交，导致更新失败.

I am guessing that this is because the new columns introduced by the alter statement have not yet been committed, so that the update fails.

我的问题是我该如何让它工作呢?我希望它作为单个事务运行，如果出现问题，我可以回滚.这很重要，因为我还有更多的更改语句要包含，但我有点沮丧，因为我无法超越这一点.

My question is how do I get this to work then? I want this to run as a single transaction that I can rollback if something goes wrong.. This is important because I have more alter statements to include yet, and am a bit frustrated that I can't get past this point.

任何帮助将不胜感激！

罗伯:)

推荐答案

即使我正在写我自己的答案 - 所有功劳都归功于 @Mikael Eriksson，他建议我需要用 GO 分隔不同的批次 - 以便更改表的代码与使用更改表的代码不冲突.谢谢迈克尔！

Even though I am writing my own answer - all credit goes to @Mikael Eriksson, who suggested that I need to separate different batches with a GO - so that the code that alters the table does not conflict with code that uses the altered table. Thanks Mikael!

USE PUK;
GO
BEGIN TRANSACTION;
BEGIN TRY

   -- - Modify RETRIEVAL_STAT
   alter table dbo.RETRIEVAL_STAT add
      SOURCE nvarchar(10) NULL,
      ACCOUNTNUMBER nvarchar(50) NULL,
      PUK nvarchar(20) NULL;

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO


USE PUK;
GO
BEGIN TRANSACTION;
BEGIN TRY

    -- transform logic.
   UPDATE      dbo.RETRIEVAL_STAT
   SET         SOURCE = 'ABC',
               ACCOUNTNUMBER = ABC.ACCOUNTNUMBER,
               PUK = ABC.PUK
   FROM        RETRIEVAL_STAT RS
   INNER JOIN  ABC
   ON          RS.SERVICE_NUMBER = ABC.SERVICENUMBER;

   UPDATE      dbo.RETRIEVAL_STAT
   SET         SOURCE = 'DEF',
               ACCOUNTNUMBER = DEF.BILLINGACCOUNTNUMBER ,
               PUK = DEF.PUK
   FROM        RETRIEVAL_STAT RS
   INNER JOIN  DEF
   ON          RS.SERVICE_NUMBER = DEF.SERVICENUMBER;

   UPDATE      dbo.RETRIEVAL_STAT
   SET         SOURCE = 'No Match'
   WHERE       SOURCE IS NULL;

    -- Fix other columns that should be not nullable.
   alter table dbo.RETRIEVAL_STAT
      alter column SERVICE_NUMBER nvarchar (50) NOT NULL;
   alter table dbo.DEF
      alter column PUK nvarchar (20) NOT NULL;


END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

这篇关于使用 Transact-SQL 在 try catch with tran 中更改然后更新的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！