使用Transact-SQL进行Alter修改然后在tran尝试捕获中更新的问题

思勤江茶业

这是一些我要通过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语句引入的新列，因此更新失败。
我的问题是我如何才能使它正常工作？我希望它可以作为单个事务运行，如果出现问题，我可以回滚。。这很重要，因为我要包含更多的alter语句，并且对我无法逾越这一点感到沮丧。
任何帮助将不胜感激！
罗伯

解决方案:


            即使我在写自己的答案，所有的功劳都归@Mikael Eriksson，他建议我需要用GO分开不同的批次-
这样，更改表的代码不会与使用更改后的表的代码冲突。谢谢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进行Alter修改然后在tran尝试捕获中更新的问题

思勤江茶业 LV1