execute stored procedure for each record in a result set without cursor

execute stored procedure for each record in a result set without cursor

Post by paul crowde » Tue, 16 Sep 2003 19:24:55



I'm sure this is a very common question, but I searched Google for about 30
minutes and couldn't find an answer.  I have a situation where I need to get
a result set, then execute a stored procedure using each record in that
result set.  Is there any way I can do this without a cursor? I've been able
to do an update like this using WHERE...IN, like this:

UPDATE Table1 SET FieldName = 'somevalue' WHERE ID IN (SELECT Table2.ID FROM
Table2 WHERE Table2.FieldName2 = 'someothervalue')

But so far I've had no luck doing a similar thing with a stored procedure.
Is this at all possible?

    Paul

 
 
 

execute stored procedure for each record in a result set without cursor

Post by David Brown » Tue, 16 Sep 2003 19:33:42



Quote:> I'm sure this is a very common question, but I searched Google for about
30
> minutes and couldn't find an answer.  I have a situation where I need to
get
> a result set, then execute a stored procedure using each record in that
> result set.  Is there any way I can do this without a cursor? I've been
able
> to do an update like this using WHERE...IN, like this:

> UPDATE Table1 SET FieldName = 'somevalue' WHERE ID IN (SELECT Table2.ID
FROM
> Table2 WHERE Table2.FieldName2 = 'someothervalue')

> But so far I've had no luck doing a similar thing with a stored procedure.
> Is this at all possible?

There is no way to do this without a cursor, and no reason to try.

For an update statement, SQLServer will create one big plan for making all
of the changes, so it's important not to use a cursor.  For a stored
procedure, SQLServer will have to run the procedure N times anyway, so
there's no benefit in avoiding the cursor.

David

 
 
 

execute stored procedure for each record in a result set without cursor

Post by paul crowde » Tue, 16 Sep 2003 19:39:37




Quote:

> There is no way to do this without a cursor, and no reason to try.

> For an update statement, SQLServer will create one big plan for making all
> of the changes, so it's important not to use a cursor.  For a stored
> procedure, SQLServer will have to run the procedure N times anyway, so
> there's no benefit in avoiding the cursor.

> David

Okay, thanks.

    Paul

 
 
 

1. Executing Stored procedures with multiple Result sets

Are there any limitations in ODBC with respect to executing stored
procedures.  I am currently using Sybase/db-lib and I routinely execute
stored procs that return multiple (different) result sets.  I know
Informix is limited to one result set per stored procedure. Does anyone
know if this is also true in Oracle.
--
Michael Gallagher
Open Link Financial

Phone: (516) 227-6600 x212

2. Constraint?

3. Books / documentation on MTS (Microsoft Transaction Server) ??

4. Executing Stored Procedure that returns result set (in Kiva)

5. THE CODE

6. Default Result Set versus Server Cursors from Stored Procedures

7. PostgreSQL JDBC Compliance

8. Executing a Stored Procedure in a Stored Procedure and selecting on the result

9. returning result sets/cursors from a stored procedure

10. Stored Procedure Cursors result set

11. Declare CURSOR on stored procedure result set

12. Declare a cursor from a stored procedure result set