T-SQL:更改事务中的视图错误并尝试捕获块

Question

我正在使用Server 2005和SQLServer2005ManagementStudioExpress。我有一个脚本和下面的方案：

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 dbo.MyView块中的错误：“保留字‘VIEW’附近的不正确语法。”

Answer 1

这没有给我任何语法错误，试试这个。我刚刚删除了动态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