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 
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