如何给SQL存储过程传递多值参数并支持单/全实体查询

需求说明 #

原存储过程仅支持传入单个实体参数（如C200、C010）进行数据查询，通过@Entitygroup参数在WHERE子句过滤实体。现需改造为满足以下三种调用场景：

• 传入单个实体参数，查询对应实体数据
• 传入Total_group参数，查询所有实体数据
• 传入多实体逗号分隔列表（如C200,C010,C300），查询指定多个实体的数据

原存储过程代码 #

ALTER PROCEDURE [DW].[SP_Fetch_Data] 
    @par_FiscalCalendarYear varchar(10), 
    @par_Entity AS varchar (10)
AS
BEGIN
    /*
    BALANCE ACCOUNTS
    */
    DECLARE @FiscalCalendarYear int  =  SUBSTRING(@par_FiscalCalendarYear,1,4)  /* 2022 */
        , @FiscalCalendarMonth int =  SUBSTRING (@par_FiscalCalendarYear,7,10) /* 11 */;
    DECLARE @FiscalCalendarPeriod int = @FiscalCalendarYear * 100 + @FiscalCalendarMonth
    DECLARE @Entitygroup varchar = @par_Entity
    
    SELECT UPPER([GeneralJournalEntry].SubledgerVoucherDataAreaId) as [Entity]
        , CONCAT(@FiscalCalendarYear, ' P', @FiscalCalendarMonth) as [Date]
        , ISNULL(ConsolidationMainAccount, '') as [Account]
        , [GeneralJournalAccountEntry].TransactionCurrencyCode as [Currency]
        , SUM([GeneralJournalAccountEntry].TransactionCurrencyAmount) as [Amount]
        , 'Import' as [Audit]
        , 'TCUR' as [DataView]
        , ISNULL([CostCenter].[GroupDimension], 'No Costcenter') as [CostCenter]
        , 'No Group' as [Group]
        , ISNULL([Intercompany].[DisplayValue], 'No Intercompany') as [Intercompany]
        , 'Closing' as [Movement]
        , ISNULL([ProductCategory].[GroupDimension], 'No ProductCategory')  as [ProductCategory]
        , ISNULL([Region].[GroupDimension], 'No Region')  as [Region]
        , ISNULL([SalesChannel].[GroupDimension], 'No SalesChannel')  as [SalesChannel]
        , 'Actual' as [Scenario]
    FROM [D365].[GeneralJournalAccountEntry]
    LEFT JOIN [D365].[GeneralJournalEntry] ON [GeneralJournalAccountEntry].GENERALJOURNALENTRY = [GeneralJournalEntry].[RECID]
        AND [GeneralJournalAccountEntry].[PARTITION] = [GeneralJournalEntry].[PARTITION]
    LEFT JOIN [D365].[FiscalCalendarPeriod] ON [GeneralJournalEntry].FiscalCalendarPeriod = FiscalCalendarPeriod.FiscalCalendarPeriod
    LEFT JOIN [DW].[MainAccounts] ON [GeneralJournalAccountEntry].MainAccount = [MainAccounts].[RECID]
    LEFT JOIN [DW].[Intercompany] ON [GeneralJournalAccountEntry].[RECID] = [Intercompany].[RECID]
    LEFT JOIN [DW].[ProductCategory] ON [GeneralJournalAccountEntry].[RECID] = [ProductCategory].[RECID]
    LEFT JOIN [DW].[Region] ON [GeneralJournalAccountEntry].[RECID] = [Region].[RECID]
    LEFT JOIN [DW].[SalesChannel] ON [GeneralJournalAccountEntry].[RECID] = [SalesChannel].[RECID]
    LEFT JOIN [DW].[CostCenter] ON [GeneralJournalAccountEntry].[RECID] = [CostCenter].[RECID]
    WHERE [EnumItemName] IN ('Revenue', 'Expense', 'BalanceSheet', 'Asset', 'Liability')
    AND [FiscalCalendarPeriod].FiscalCalendarPeriodInt <= @FiscalCalendarPeriod
    AND [GeneralJournalEntry].SubledgerVoucherDataAreaId <= @Entitygroup
    GROUP BY UPPER([GeneralJournalEntry].SubledgerVoucherDataAreaId)
        , ISNULL(ConsolidationMainAccount, '')
        , [GeneralJournalAccountEntry].TransactionCurrencyCode
        , ISNULL([CostCenter].[GroupDimension], 'No Costcenter')
        , ISNULL([Intercompany].[DisplayValue], 'No Intercompany')
        , ISNULL([ProductCategory].[GroupDimension], 'No ProductCategory')
        , ISNULL([Region].[GroupDimension], 'No Region')
        , ISNULL([SalesChannel].[GroupDimension], 'No SalesChannel')
END

改造后的存储过程代码 #

ALTER PROCEDURE [DW].[SP_Fetch_Data] 
    @par_FiscalCalendarYear varchar(10), 
    @par_Entity AS varchar(MAX)  -- 修改为MAX长度，支持多实体列表
AS
BEGIN
    /*
    BALANCE ACCOUNTS
    */
    DECLARE @FiscalCalendarYear int  =  SUBSTRING(@par_FiscalCalendarYear,1,4)  /* 2022 */
        , @FiscalCalendarMonth int =  SUBSTRING (@par_FiscalCalendarYear,7,10) /* 11 */;
    DECLARE @FiscalCalendarPeriod int = @FiscalCalendarYear * 100 + @FiscalCalendarMonth
    
    -- 表变量存储拆分后的实体列表
    DECLARE @EntityList TABLE (EntityCode varchar(10))
    
    -- 处理参数：如果是Total_group则不填充实体列表，否则拆分逗号分隔的实体
    IF @par_Entity <> 'Total_group'
    BEGIN
        -- 拆分逗号分隔的实体参数
        DECLARE @StartIndex int = 1, @CommaIndex int
        WHILE @StartIndex <= LEN(@par_Entity)
        BEGIN
            SET @CommaIndex = CHARINDEX(',', @par_Entity, @StartIndex)
            IF @CommaIndex = 0 SET @CommaIndex = LEN(@par_Entity) + 1
            
            INSERT INTO @EntityList (EntityCode)
            VALUES (LTRIM(RTRIM(SUBSTRING(@par_Entity, @StartIndex, @CommaIndex - @StartIndex))))
            
            SET @StartIndex = @CommaIndex + 1
        END
    END
    
    SELECT UPPER([GeneralJournalEntry].SubledgerVoucherDataAreaId) as [Entity]
        , CONCAT(@FiscalCalendarYear, ' P', @FiscalCalendarMonth) as [Date]
        , ISNULL(ConsolidationMainAccount, '') as [Account]
        , [GeneralJournalAccountEntry].TransactionCurrencyCode as [Currency]
        , SUM([GeneralJournalAccountEntry].TransactionCurrencyAmount) as [Amount]
        , 'Import' as [Audit]
        , 'TCUR' as [DataView]
        , ISNULL([CostCenter].[GroupDimension], 'No Costcenter') as [CostCenter]
        , 'No Group' as [Group]
        , ISNULL([Intercompany].[DisplayValue], 'No Intercompany') as [Intercompany]
        , 'Closing' as [Movement]
        , ISNULL([ProductCategory].[GroupDimension], 'No ProductCategory')  as [ProductCategory]
        , ISNULL([Region].[GroupDimension], 'No Region')  as [Region]
        , ISNULL([SalesChannel].[GroupDimension], 'No SalesChannel')  as [SalesChannel]
        , 'Actual' as [Scenario]
    FROM [D365].[GeneralJournalAccountEntry]
    LEFT JOIN [D365].[GeneralJournalEntry] ON [GeneralJournalAccountEntry].GENERALJOURNALENTRY = [GeneralJournalEntry].[RECID]
        AND [GeneralJournalAccountEntry].[PARTITION] = [GeneralJournalEntry].[PARTITION]
    LEFT JOIN [D365].[FiscalCalendarPeriod] ON [GeneralJournalEntry].FiscalCalendarPeriod = FiscalCalendarPeriod.FiscalCalendarPeriod
    LEFT JOIN [DW].[MainAccounts] ON [GeneralJournalAccountEntry].MainAccount = [MainAccounts].[RECID]
    LEFT JOIN [DW].[Intercompany] ON [GeneralJournalAccountEntry].[RECID] = [Intercompany].[RECID]
    LEFT JOIN [DW].[ProductCategory] ON [GeneralJournalAccountEntry].[RECID] = [ProductCategory].[RECID]
    LEFT JOIN [DW].[Region] ON [GeneralJournalAccountEntry].[RECID] = [Region].[RECID]
    LEFT JOIN [DW].[SalesChannel] ON [GeneralJournalAccountEntry].[RECID] = [SalesChannel].[RECID]
    LEFT JOIN [DW].[CostCenter] ON [GeneralJournalAccountEntry].[RECID] = [CostCenter].[RECID]
    WHERE [EnumItemName] IN ('Revenue', 'Expense', 'BalanceSheet', 'Asset', 'Liability')
    AND [FiscalCalendarPeriod].FiscalCalendarPeriodInt <= @FiscalCalendarPeriod
    -- 调整实体过滤逻辑
    AND (
        @par_Entity = 'Total_group'  -- Total_group时不过滤实体
        OR [GeneralJournalEntry].SubledgerVoucherDataAreaId IN (SELECT EntityCode FROM @EntityList)
    )
    GROUP BY UPPER([GeneralJournalEntry].SubledgerVoucherDataAreaId)
        , ISNULL(ConsolidationMainAccount, '')
        , [GeneralJournalAccountEntry].TransactionCurrencyCode
        , ISNULL([CostCenter].[GroupDimension], 'No Costcenter')
        , ISNULL([Intercompany].[DisplayValue], 'No Intercompany')
        , ISNULL([ProductCategory].[GroupDimension], 'No ProductCategory')
        , ISNULL([Region].[GroupDimension], 'No Region')
        , ISNULL([SalesChannel].[GroupDimension], 'No SalesChannel')
END

改造要点说明 #

• 参数类型调整：将@par_Entity从varchar(10)改为varchar(MAX)，支持传入任意长度的多实体逗号分隔列表
• 实体列表处理：新增表变量@EntityList，用于存储拆分后的单个实体代码；当传入非Total_group参数时，自动拆分逗号分隔的字符串并填充到表变量中
• 过滤逻辑优化：WHERE子句中新增条件分支，当传入Total_group时跳过实体过滤，否则匹配表变量中的实体列表
• 修复潜在问题：移除原代码中未指定长度的@Entitygroup变量（原varchar默认长度为1，会导致参数截断），直接通过参数和表变量处理实体过滤

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