SQL Server 将多个值作为参数传递到存储过程中

我有一个传递参数的存储过程。这些参数由另一个工具指示。其中一个参数有一个实体列表，如C200，C010等。
但要求是，将从另一个工具（Fluence）运行存储过程的人应该能够调用每个实体，而且还能够检索与所有实体相关的数据。
我有这里显示的SQL代码，如果你一次选择一个实体，它可以完美地工作。在Where子句中，我根据@Entitygroup过滤它，也就是Declared。从另一个工具获取所有的Entity都是在Total_group参数名处传递的。

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')

（作为评论会很混乱）
你的意思是如果@par_entity不为空并且有一个''以外的值，那么就使用else继续，就好像它根本不存在一样吗？然后你可以修改你的代码：

DECLARE @Entitygroup varchar = @par_Entity

收件人：

DECLARE @Entitygroup varchar = case 
             when @par_Entity is not null then @par_Entity 
             else '' 
           end;

以及：

AND [GeneralJournalEntry].SubledgerVoucherDataAreaId <= @Entitygroup

收件人：

AND (@Entitygroup = '' OR [GeneralJournalEntry].SubledgerVoucherDataAreaId <= @Entitygroup)

PS：性能方面，它不会是最佳的。
编辑：您也可以将其设置为可能的最大值时，它没有通过。

DECLARE @Entitygroup varchar = case 
                 when @par_Entity is null or @par_entity = '' then 'zzzzzz' 
                 else @par_Entity
               end;

AND [GeneralJournalEntry].SubledgerVoucherDataAreaId <= @Entitygroup

SQL Server 将多个值作为参数传递到存储过程中

1条答案

相关问题

热门标签

最新问答