November 3, 2009 at 3:06 pm
Hello,
I have a cursor that is running a stored procedure (RPT_TargetDatesReport) for a set of IDs. I have over 150 datasets that are being returned and I need to combine them into one large dataset or table.
How would I go about doing that? I heard that you could make a temp table, but I am having no luck. Thanks so much!!
Declare
@entity_sk int
DECLARE myCursor CURSOR FOR
(My Very long query returning all the @entity_sk IDs to run
)
OPEN myCursor
FETCH NEXT FROM myCursor INTO @entity_sk
WHILE @@FETCH_STATUS = 0
BEGIN
exec RPT_TargetDatesReport @entity_sk
FETCH NEXT FROM myCursor INTO @entity_sk
END
CLOSE myCursor
DEALLOCATE myCursor
November 3, 2009 at 3:14 pm
What is happening inside of RPT_TargetDatesReport? If you need the output all in one data set, I would recomend writing a version of RPT_TargetDatesReport that works off of a set of data as opposed to one row per execution.
November 3, 2009 at 3:25 pm
There is a set of 138 date functions that run within the stored procedure. Much of the data in the database is historical, so it is necessary to run a "top 1" or some other type function to get one field out. The Stored procedure is over 500 lines long and I have inherited it from another person. Originally, the stored procedure was called only once for one ID. However, there is a need to report all of the IDs together. One ID per record.
With that said, I'm trying to avoid changing it.
November 3, 2009 at 3:36 pm
I assume that the result set returned by the stored proc is the same as far as number of columns/data types, etc?
If so, you may want to create a temporary table then do the following:
insert into #MyTable
exec RPT_TargetDatesReport @entity_sk
November 3, 2009 at 3:38 pm
Of course, the right way to do it is to rewrite the process as a set-based query.
November 3, 2009 at 3:39 pm
Well, if the SP is returning a dataset and you need to comine them, the only other way I can see would be to use INSERT INTO EXEC to get the result set into a temporary table.
November 3, 2009 at 3:41 pm
Man, you're fast Lynn. You got 2 posts done in the amout of time took me to write out a one sentance post!
November 3, 2009 at 3:55 pm
Thank you! I just got it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply