| Author |
Stored Procedure as Cursor
|
|
| David_from_Chicago 2006-10-24, 6:29 pm |
| I am using SQL Server 2000 - 8.0. I would like to use a stored
procedure which returns a recordset of dates as a cursor within another
stored procedure. Basically I want to loop through a set of dates and
pull back some additional data for each date returned. The entire
result set will eventually be inserted into a new table (like a
cross-tab or pivot table).
It seems that to do this I must ue the OPENQUERY command in SQL Server.
I was hoping that the @@SERVERNAME variable would work for this since
both procedures I want to call are on the same box. Here's the jist of
my code:
CREATE PROCEDURE usp_DateReport
AS
DECLARE @MyDate datetime
DECLARE DateCursor CURSOR FOR
SELECT *
FROM OPENQUERY(@@SERVERNA
ME
'exec usp_GETsomeDates'
)
OPEN DateCursor
FETCH NEXT FROM DateCursor
INTO @MyDate
-- Retrieve values
WHILE @@FETCH_STATUS = 0
BEGIN
--get additional data based on @MyDate
PRINT @MyDate
END
CLOSE DateCursor
DEALLOCATE DateCursor
Cheers,
David_from_Chicago
| |
| John Bell 2006-10-24, 6:29 pm |
| Hi David
You could run the procedure into a temporary table (INSERT..EXEC) and then
cursor through that rather than using OPENQUERY.
John
"David_from_Chicago" wrote:
> I am using SQL Server 2000 - 8.0. I would like to use a stored
> procedure which returns a recordset of dates as a cursor within another
> stored procedure. Basically I want to loop through a set of dates and
> pull back some additional data for each date returned. The entire
> result set will eventually be inserted into a new table (like a
> cross-tab or pivot table).
>
> It seems that to do this I must ue the OPENQUERY command in SQL Server.
> I was hoping that the @@SERVERNAME variable would work for this since
> both procedures I want to call are on the same box. Here's the jist of
> my code:
>
>
> CREATE PROCEDURE usp_DateReport
> AS
> DECLARE @MyDate datetime
>
> DECLARE DateCursor CURSOR FOR
> SELECT *
> FROM OPENQUERY(@@SERVERNA
ME
> 'exec usp_GETsomeDates'
> )
> OPEN DateCursor
>
> FETCH NEXT FROM DateCursor
> INTO @MyDate
>
> -- Retrieve values
> WHILE @@FETCH_STATUS = 0
> BEGIN
> --get additional data based on @MyDate
> PRINT @MyDate
> END
>
> CLOSE DateCursor
> DEALLOCATE DateCursor
>
> Cheers,
> David_from_Chicago
>
>
| |
| John Bell 2006-10-24, 6:29 pm |
| ... or you could use a table function.
John
"John Bell" wrote:
[color=darkred]
> Hi David
>
> You could run the procedure into a temporary table (INSERT..EXEC) and then
> cursor through that rather than using OPENQUERY.
>
> John
>
> "David_from_Chicago" wrote:
>
| |
| David_from_Chicago 2006-10-24, 6:29 pm |
| Pardon my inexperience with SQL Server, but how might I run the stored
procedure into a temporary table within another stored proc? This
seems that I would run into the same kind of issue I had before
(requiring an openquery call).
Any example of how I might do this in the simplest way would help.
Thanks.
John Bell wrote:
> Hi David
>
> You could run the procedure into a temporary table (INSERT..EXEC) and then
> cursor through that rather than using OPENQUERY.
>
> John
| |
| Scott Morris 2006-10-24, 6:29 pm |
| "David_from_Chicago" <dpfollmer@yahoo.com> wrote in message
news:1158676947.250366.131510@k70g2000cwa.googlegroups.com...
> Pardon my inexperience with SQL Server, but how might I run the stored
> procedure into a temporary table within another stored proc? This
> seems that I would run into the same kind of issue I had before
> (requiring an openquery call).
There is a specific form of the INSERT statement that allows you to execute
a stored procedure. Example G is provided for the insert statement in the
2000 version of BOL. However, as John indicated, you might want to
consider using a table-valued function for this (assuming usp_GETsomeDates
can be converted - seems possible based on the name).
|
|
|
|