SSRS Multi-Select and For-Each Stored-Procs-CodePudding

I am using an SSRS multi-select which gives me an unknown number of values > 0. That is something like the following (1),(4),...(x).

I want to union the results of a stored procedure for each ID that SQL receives.

If this came as a table my job would be easier.

ID
1
4
...
x

I found the following code got me a good way there:

DECLARE @MyTempTable TABLE (txtType NVARCHAR(20), intID integer, txtAccountCode NVARCHAR(50), txtSchoolID NVARCHAR(50), txtSageCode NVARCHAR(50), ChargeCode NVARCHAR(50),
       txtDescription NVARCHAR(200), mAmount DECIMAL(18,2), percentageAmount DECIMAL(18,2), txtChargeType NVARCHAR(50), txtNominalAccount NVARCHAR(50), bBilledInAdvance BIT)

DECLARE @i int
DECLARE @CycleId int
DECLARE @numrows int
DECLARE @Cycle Table (
    idx smallint IDENTITY(1,1)
    , CycleId int
)

INSERT @Cycle (CycleId)
SELECT [TblFeeBillingCycleID] FROM [TblFeeBillingCycle] WHERE  [TblFeeBillingCycleID] IN(@intCycleId)

SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @Cycle)
IF @numrows > 0
    WHILE (@i <= (SELECT MAX(idx) FROM @Cycle))
    BEGIN

        SET @CycleId = (SELECT CycleId FROM @Cycle WHERE idx = @i)

        INSERT  INTO @MyTempTable
        EXEC       usp_cust_GetTransactionsByCycle @CycleId
        SET @i = @i   1

        SET @i = @i   1
    END

If I could either unpivot my row of unknown length and unknown columns then this would work I think.

I also thought there was a way to insert into a table by wrapping values, so it chops a long row of data into x rows matching the length of the new table.

Then again there may be another way to iterate up a table of unknown length and unknown columns names.

SQL might be the wrong way to go, I just fear SSRS is pushing me in this direction, I don't know if I can do the foreach in SSRS instead.

CodePudding user response：

If the question is about how to transform string into table then yuo can use STRING_SPLIT

 DECLARE @InString VARCHAR(20);
 SET @InString='1,2,3,4,5,6,7,8,9,10';
 SELECT VALUE FROM string_split(@InString,',');

CodePudding user response：

I think it's a kind of madness but I found a workaround to get a table of values from the results from SSRS. I query the IDs against a source table using IN().

SELECT [TblFeeBillingCycleID] 
FROM [TblFeeBillingCycle] 
WHERE [TblFeeBillingCycleID] IN(@intCycleId)