Q: SSRS Multi-Select and For-Each Stored-Procs 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.

A: 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,','); 


A: 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)

