代码之家  ›  专栏  ›  技术社区  ›  Willy

T-SQL:事务和try-catch块中的ALTER VIEW错误

  •  2
  • Willy  · 技术社区  · 10 年前

    我使用的是SQL Server 2005和SQL Server 2005 Management Studio Express。我有一个具有以下方案的脚本:

    USE [MyDatabase]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    BEGIN TRANSACTION;
    GO
    
    BEGIN TRY
        ALTER VIEW dbo.MyView
        AS
        SELECT ...
        GO
        ALTER TABLE ...
        GO
        UPDATE dbo.MyTable ...
        GO
        INSERT INTO dbo.AnotherTable ...
        GO
    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
    

    问题是我在ALTER VIEW dbo.MyView块中收到错误: “保留字'VIEW'附近语法错误。”

    1 回复  |  直到 10 年前
        1
  •  2
  •   sanmis    10 年前

    这没有给我任何语法错误,试试看。我刚刚删除了动态sql末尾的“Go”。

    USE [MyDatabase]
    go
    SET ANSI_NULLS ON
    go
    SET QUOTED_IDENTIFIER ON
    go
    BEGIN TRANSACTION
    go
    BEGIN TRY
    EXEC sp_executesql N'ALTER VIEW dbo.MyView
    AS
    SELECT * FROM ....; '
    EXEC sp_executesql N'ALTER TABLE ...;'
    EXEC sp_executesql N'UPDATE ....;'
    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