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

Question

我有一个将参数传递给它的存储过程。这些参数由另一个工具表示。其中一个参数有一个实体列表，如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')

Answer 1

(作为注释会很乱)

你的意思是如果@par_entity不是空的，有一个值以外的"然后使用，否则继续，如果它不存在吗?然后你可以修改你的代码:

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