|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Building cursors dynamically
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Building cursors dynamically
|
|
| John Fleming 2002-11-20, 1:24 pm |
| Hello,
I would like to create a stored procedure in a SQL 2000 database which
utilizes two cursors, one based on a SQL query, and a second which is
instantiated as an empty cursor. The procedure will then iterate
through the recordset of the populated cursor, make a procedural call
using data in the recordset and append rows to the empty cursor based
on return data from these calls. Once the empty cursor is populated,
I would like to return its data as the return recordset of the stored
procedure.
This tasks involves a few steps of which I am not sure whether or not
they are possible. The first is the ability to instantiate an empty
cursor, the second is the ability to append rows to a cursor and the
third is the ability to return the contents of a cursor as a resultant
recordset from a stored procedure.
If anyone can help me with any of these issues, it would be greatly
appreciated. Alternatively, if anyone knows of an easier way I could
go about accomplishing the same feat, please let me know. I am
attempting to do this in order to avoid making multiple calls to a
database. Thanks in advance.
John Fleming
johnf@tdwweb.com
| |
| Doug Miller 2002-11-20, 6:23 pm |
| flem@cybercomm.net (John Fleming) wrote in message news:<34d5f642.0211201116.135d46c0@posting.google.com>...
> they are possible. The first is the ability to instantiate an empty
> cursor, the second is the ability to append rows to a cursor and the
> third is the ability to return the contents of a cursor as a resultant
> recordset from a stored procedure.
I'd do it with a temp table myself. Use DML to create the table, do
all the stuff you are talking about, and at teh end of the stored
procedure, do a select on the temp table to get teh record set back to
the calling app.
Often, if you get really creative, you don't have to have the first
loop or second loop. You can just pull the data directly from the
original source as a Select.
Sometimes you can pull the data into a temp table, do some
manipulation on that table, then do a select.
Rarely do you have to pull the data into a cursor and loop through it
row by row, but there are instances.
-doug miller
|
|
|
|
|