Posted by Hugo Kornelis on 11/21/07 21:34

On Wed, 21 Nov 2007 11:49:33 -0800 (PST), Winbug wrote:

>hi
>I have a scenario where I have created a stored procedure wich task is
>to search in my database with certain criterias. For each record from
>this search, I want to run a couple of other stored procedure.
>
>
>Here is some code:
>
>decare $myid nvarchar(15)
>
>select id, desc, customer from project where customer = 'cust1'
>------------------------------------------
>--HERE IS CODE MISSING--
>------------------------------------------
>--for each of the records returned, I want to run 4 stored procedures
>with the id as parameter.
>begin
>execute StoredProcedure1 $myid
>execute StoredProcedure2 $myid
>execute StoredProcedure3 $myid
>execute StoredProcedure4 $myid
>end
>
>The problem is I don't know how to get hold of the result set and loop
>through. Maybe you can help me.
>
>regards
>Roger

Hi Roger,

From a performance point of view, this is not a good way to build your
application. You should rewrite the four stored procedures to operate on
all qualifying data at once, as SQL Server is optimized for processing
set-based queries.

However, there may be reasons that this is not feasible. The cost of
rewriting the procs may be high, and if your amount of data is low,
performance might not be a consideration. In that case, you can use a
cursor to process the results of a query row by row. There are many
examples in Books Online, so if you just use the index to find DECLARE
CURSOR, you should be all set.

If performance does matter and you still want to use a cursor, than you
might wish to read my recent blog posts on the performance effects of
various cursor options. You can find it at
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]