Post reply

Return loop results in single resultset

  • June 23, 2017 at 1:51 am

    #338393

    Hi,

    I would like a report I'm running to loop a number of times but for the results to be displayed in one single output instead of reach loop running like an independent query and having column headers each time. The reason is i want to export the results to Excel and if I do the loop 100 times I'll need to export it 100 times. 

    I've created a small example below to try to explain a little better

    declare @num int = 1
    while @num < 100
    begin
        select @num
        set @num=@num+1
    end

    Results:
    (No column name)
    1
    (No column name)
    2
    (No column name)
    3
    etc

    Desired result
    (No column name)
    1
    2
    3

    Thank you for any advice

  • June 23, 2017 at 2:01 am

    #1947892

    I know the example you posted is probably a massive simplification, but for that you don't need a loop at all.  It's likely that you'll be able to rewrite your actual routine as a single set-based query as well.  If you can't, then the answer to your question is to insert the result set of each iteration into a temp table, and select from that at the end.

    John

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply