OK say I want to execute the following stored procedure for every order that
matches specific criteria
EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
If my set of orders to process is returned by this query:
select lngOrderID from tblOrders where intstatus=3
Is there any way to do this without resorting to a cursor?"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23RYZxTnXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
> OK say I want to execute the following stored procedure for every order
> that matches specific criteria
>
> EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
>
> If my set of orders to process is returned by this query:
> select lngOrderID from tblOrders where intstatus=3
>
> Is there any way to do this without resorting to a cursor?
>
No. And cursors aren't _that_ slow. Executing the procedure for each row
would not be noticably better without a cursor.
The potential performance problem is that you are executing the logic of the
procedure on a row-wise basis, instead of a set-wise basis. The only way to
avoid the row-wise processing would be to unwrap the guts of
dbo.sprPassUpline and create a version which operated over the entire set of
rows. Something like
EXECUTE @.RC = dbo.sprPassUplineByStatus 3
David|||declare @.loop int
declare @.rowcount int
declare @.lngOrder int
declare @.RC int
create table #OrderList (
ROW_ID int identity ,
lngOrder int not null )
insert into #OrderList ( lngOrder ) select lngOrderID from tblOrders where
intStatus = 3
declare @.loop = min(ROW_ID), @.rowcount = max(ROW_ID) from #OrderList
while @.loop <= @.rowcount
begin
select @.lngOrder = lngOrder from #OrderList where ROW_ID = @.loop
exec @.RC = dbo.sprPassUpline @.lngOrder
if @.RC <>
begin
-- do whatever
end
select @.loop = @.loop + 1
-- or the below method if you delete any rows from the #OrderList table
for any reason
-- select @.loop= min(ROW_ID) from #OrderList where ROW_ID > @.loop
end
drop table #OrderList
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23RYZxTnXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
> OK say I want to execute the following stored procedure for every order
> that matches specific criteria
>
> EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
>
> If my set of orders to process is returned by this query:
> select lngOrderID from tblOrders where intstatus=3
>
> Is there any way to do this without resorting to a cursor?
>|||"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:O8e%f.18404$ic1.16436@.newsfe5-win.ntli.net...
> declare @.loop int
> declare @.rowcount int
> declare @.lngOrder int
> declare @.RC int
> create table #OrderList (
> ROW_ID int identity ,
> lngOrder int not null )
> insert into #OrderList ( lngOrder ) select lngOrderID from tblOrders where
> intStatus = 3
> declare @.loop = min(ROW_ID), @.rowcount = max(ROW_ID) from #OrderList
> while @.loop <= @.rowcount
> begin
> select @.lngOrder = lngOrder from #OrderList where ROW_ID = @.loop
> exec @.RC = dbo.sprPassUpline @.lngOrder
> if @.RC <>
> begin
> -- do whatever
> end
> select @.loop = @.loop + 1
> -- or the below method if you delete any rows from the #OrderList table
> for any reason
> -- select @.loop= min(ROW_ID) from #OrderList where ROW_ID > @.loop
> end
> drop table #OrderList
>
OK, that is tecnically what the OP asked for, but in what possible way is
that better than using a cursor?
declare cOrders cursor local static for
select lngOrderID from tblOrders
where intStatus = 3
declare @.lngOrder int,
@.RC int
open cOrders
fetch next from cOrders into @.lngOrder
while @.@.fetch_status = 0
begin
exec @.RC = dbo.sprPassUpline @.lngOrder
if @.RC <> 0
begin
raiserror('sprPassUpline failed returning %d',16,1,@.RC)
end
fetch next from cOrders into @.lngOrder
end
close cOrders
?
Davud|||> Is there any way to do this without resorting to a cursor?
Possibly, but since you haven't told us what the SP does we can't tell you.
The question is not "How do I do X once for each row without a cursor?"
The question is "How do I do X for the whole set INSTEAD OF once for each
row?"
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Well actually in normal day to day business this SP is only called for one
row at a time when that order enters a given state. But when initializing
new systems it must be called for everything on import. Guess that is a
good enough reason to redo the SP and just make it set based so it'll handle
one or more. We never had this requirement before so it was never an issue.
Thanks for jumping in everybody.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uvSpxznXGHA.5012@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to do this without resorting to a cursor?
> Possibly, but since you haven't told us what the SP does we can't tell
> you.
> The question is not "How do I do X once for each row without a cursor?"
> The question is "How do I do X for the whole set INSTEAD OF once for each
> row?"
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
