使用 Transact-SQL 在 try catch 中使用 tran 进行 alter then update 时出现问题

Question

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

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语句引入的新列尚未提交，因此更新失败。

我的问题是我该如何让它发挥作用？我希望它作为单个事务运行，如果出现问题，我可以回滚。这很重要，因为我还有更多的更改语句要包含，并且对我无法超越这一点感到有点沮丧。

任何帮助将不胜感激！

抢 :)

Answer 1

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

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

Answer 2

SQL Server 在编译代码时检查列。您可以使用动态 sql 来解决该问题。

-- transform logic.
declare @SQL nvarchar(1000)
set @SQL = 
  'update dbo.RETRIEVAL_STAT set
     SOURCE = @S,
     ACCOUNTNUMBER = @A,
     PUK = @P';

declare @Params nvarchar(1000);
set @Params = N'@S nvarchar(10), @A nvarchar(10), @P nvarchar(20)';

exec sp_executesql @SQL, @Params, N'S', N'A', N'P';