Azure Synapse专用池创建含EXECUTE AS的存储过程报错求助

问题背景 #

尝试在Azure Synapse专用池中创建源自Microsoft AdventureWorks2019示例数据库的HumanResources.uspUpdateEmployeeLogin存储过程，修改了Synapse不支持的数据类型后执行脚本，遇到语法错误。

原始SSMS中的存储过程脚本 #

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
    @BusinessEntityID [int], 
    @OrganizationNode [hierarchyid],
    @LoginID [nvarchar](256),
    @JobTitle [nvarchar](50),
    @HireDate [datetime],
    @CurrentFlag [dbo].[Flag]
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [OrganizationNode] = @OrganizationNode 
            ,[LoginID] = @LoginID 
            ,[JobTitle] = @JobTitle 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [BusinessEntityID] = @BusinessEntityID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;
GO

修改后用于Synapse的脚本 #

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
    @BusinessEntityID [int], 
    @OrganizationNode [nvarchar](100),
    @LoginID [nvarchar](256),
    @JobTitle [nvarchar](50),
    @HireDate [datetime],
    @CurrentFlag [bit]


WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [OrganizationNode] = @OrganizationNode 
            ,[LoginID] = @LoginID 
            ,[JobTitle] = @JobTitle 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [BusinessEntityID] = @BusinessEntityID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

错误信息 #

Parse error at line: 10, column: 1: Incorrect syntax near 'WITH'.

解决方案 #

Azure Synapse专用池的T-SQL语法不支持WITH EXECUTE AS CALLER子句，这是导致语法错误的直接原因。

修改步骤：

• 移除WITH EXECUTE AS CALLER行
• 确保参数列表后直接衔接AS关键字

修改后的正确脚本：

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
    @BusinessEntityID [int], 
    @OrganizationNode [nvarchar](100),
    @LoginID [nvarchar](256),
    @JobTitle [nvarchar](50),
    @HireDate [datetime],
    @CurrentFlag [bit]
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [OrganizationNode] = @OrganizationNode 
            ,[LoginID] = @LoginID 
            ,[JobTitle] = @JobTitle 
            ,[HireDate] = @HireDate 
            ,[CurrentFlag] = @CurrentFlag 
        WHERE [BusinessEntityID] = @BusinessEntityID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

额外注意：Synapse专用池对T-SQL有诸多限制，比如不支持hierarchyid、自定义数据类型（如原脚本中的[dbo].[Flag]），你已完成的类型替换是正确的。另外，若存储过程中的错误处理依赖[dbo].[uspLogError]，需确保该存储过程也适配Synapse的语法要求。

内容的提问来源于stack exchange，提问作者Av1n4sh