union on the output of two stored procedures

  • Is there a way to use a union on the output of two stored procedures with in a while loop?

     

    I have a stored procedure that works it accepts one parameter and gives back 3 columns of data 42 rows long.

     

    I would like to be able to have a user specify how many times he would like this sp to run and give it different parameters for each running.  This is what I have so far

     

     

     

     

    declare @TimesToRun int

            ,@blkidM int

            ,@blkid1 int

            ,@blkid2 int

            ,@blkid3 int

     

     

    set @TimesToRun = 3

    set @blkid1 = 218645

    set @blkid2 = 218646

    set @blkid3 = 218647

    set @blkidM = @blkid1

     

    while @TimesToRun > 0

     

    begin

     

    execute dbo.DensityGrad @blkid = @blkidM

     

    set @TimesToRun = @TimesToRun – 1

     

    set @blkidM = @blkid2

     

    end

  • Just Create A tempTable with the structure of the Recordset returned by the sp and use the insert exec construct like:

    declare @TimesToRun int

            ,@blkidM int

            ,@blkid1 int

            ,@blkid2 int

            ,@blkid3 int

     

     

    set @TimesToRun = 3

    set @blkid1 = 218645

    set @blkid2 = 218646

    set @blkid3 = 218647

    set @blkidM = @blkid1

     

     Create Table #T (fld1 ,fld2 ,fld3..)

     

    while @TimesToRun > 0

     

    begin

     

    INSERT #T (fld1 ,fld2 ,fld3..) execute dbo.DensityGrad @blkid = @blkidM

     

    set @TimesToRun = @TimesToRun – 1

     

    set @blkidM = @blkid2

     

    end

     

    Select * from #T1

     

    you will also have to change your parameter to use a list of values sepparated by a comma or with equal spacing so that you can automate the parameter passing too!

     

    HTH

     


    * Noel

  • Thank you that is a great idea. 

    I have a question about this part though

    "you will also have to change your parameter to use a list of values sepparated by a comma or with equal spacing so that you can automate the parameter passing too!"

    could I get an example of how to do this

    please

    Cory McRae

  • you can create a function to parse the string.

    this one was taken from the script library on this site and adapted to your case:

    CREATE FUNCTION dbo.udf_GetValueFromList( @List varchar(1000), @position int)

    RETURNS varchar(100)

    AS

    BEGIN

    -- Returns a ZERO based value from a comma separated list

    DECLARE @i int,@j int, @Pos int,  @value varchar(100)

    SELECT @i = 0, @j-2 = 0, @pos = 0

    IF SUBSTRING (@List, LEN (@List), 1) <> ','

    BEGIN

       SELECT @List = @List + ','

    END

    SELECT @i = CHARINDEX (',', @List, @i + 1)

    WHILE @i > 0

    BEGIN

       SELECT @value = SUBSTRING (@List, @j-2+1, (@i - @j-2) -1)

       IF @pos = @position

        BREAK

      

       SELECT @j-2 = @i, @pos = @Pos + 1

       SELECT @i = CHARINDEX (',' , @List, @i + 1)

    END

    RETURN  @value

    END

     

    then on your procedure just pass the next index to the procedure like:

    declare @listofValues  varchar(1000), @n int, @x int

    Select @listOfValues ='1234,3456,789,2365', @n = 0

    and change:

    INSERT #T (fld1 ,fld2 ,fld3..) execute dbo.DensityGrad @blkid = @blkidM

     

    set @TimesToRun = @TimesToRun – 1

     

    set @blkidM = @blkid2

    to:

     select  @x = cast (dbo.udf_GetValueFromList( @listOfValues , @n&nbsp as int)

     

     INSERT #T (fld1 ,fld2 ,fld3..) execute dbo.DensityGrad @x

     

    select @TimesToRun = @TimesToRun – 1,  @n = @n + 1

     

     

    HTH


    * Noel

  • I will have to look up string funtions I have not done a lot with them befor

     

    thank you for all your help

    Cory McRae

Viewing 5 posts - 1 through 4 (of 4 total)

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