I have a stored procedure with one param and gives me one result. It takes a
SKU varcahr and sends back it's price (I don't use output param).
I need to call this procedure from within another procedure several times
(cursor loop), something like this
mycursor fetch @sku
set @myprice = exec mySP @sku
do something
fetch next
Is it possible, if yes, what should be the syntax to call mySP

Thanks in advance

An Output parameter is the proper way to do this but you can try this:

EXEC @myprice = [dbo].[mySP] @sku


Show quoteHide quote

On Fri, 25 Nov 2005 12:57:34 -0800, SalamElias wrote:

Hi SalamElias,

The best you can do is to rewrite the stored procedure's logic to handle
all rows at once, then also change the "do something" to "do something
for all rows at once". SQL Server is optimized for set-based processing;
row by row processing is slower in at least 99% of all cases.

If that's not possible in your case, then at least rewrite the procedure
to use an output parameter. The return value of a stored procedure can
only be an integer, since the return value is intended to be used to
return a success/failure status only. A price will usually not be an
integer.
Then, use
  EXEC mySp @sku, @result = @myprice OUT

Or, if you insist on kluding a price in an integer return value:
  EXEC @myprice = mySp @sku

Best, Hugo

Either incorporate the logic from the procedure into your query, thus
achieving a set-based solution, or design a scalar user-defined function
based on the logic in your stored procedure.

As Hugo already suggested, SQL is much more efficient in set-based processing.


ML

Trasform columns to rows
Still struggling w/ LAST transaction date!!!
DISTINCT MonthName for a lot of dates....
selecting from a 6 month date range
Sum() And Nulls