avoiding cursors how to do this?

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
> --
>

avoiding cursors how to do this?

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...
> 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
> --
>

avoid using cursors....

I am trying to rewrite a sp that I created years ago to avoid using cursors
so there is no problems with mutiprocessor systems and parallelism.
Simplifying, we have an order table and task table for each order:
CREATE TABLE [TOrders] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (64) COLLATE Modern_Spanish_CI_AS NULL ,
CONSTRAINT [PK_TOrders] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [TTasks] (
[OrderID] [int] NOT NULL ,
[ID] [int] NOT NULL ,
[Description] [varchar] (64) COLLATE Modern_Spanish_CI_AS NOT NULL
CONSTRAINT [PK_TTasks] PRIMARY KEY NONCLUSTERED
(
[OrderID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_TTasks_TOrders] FOREIGN KEY
(
[OrderID]
) REFERENCES [TOrders] (
[ID]
)
) ON [PRIMARY]
GO
The stored procedure under analysis consists of copying (inserting) all the
tasks already existing for an order to another one. Since ID field in TTasks
is not identity, we should retrieve the current maximum ID for the
destination order and continue the insertions from that ID onwards. The SP
as it is now follows:
CREATE PROCEDURE CopyTasksFromOrderToOrder (@.fromO int, @.toO int) AS
DECLARE
@.i int,
@.Description varchar(64)
-- Retrieve the currently maximum ID for the destination order
SELECT @.i = ISNULL(MAX(ID),0) FROM TTasks WHERE OrderID = @.toO
-- Cursor to iterate through source taks
DECLARE my_cursor CURSOR LOCAL FOR
SELECT TTasks.Description FROM TTasks
WHERE TTasks.OrderID= @.fromO
ORDER BY TTasks.ID
-- This is a simple iteration to insert tasks but starting at @.i instead
starting at 1
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @.Description
WHILE (@.@.FETCH_STATUS <> -1) BEGIN
IF (@.@.FETCH_STATUS <> -2) BEGIN
SET @.i = @.i + 1
INSERT INTO TTasks (OrderID, ID, Description)
VALUES (@.toO, @.i, @.Description)
END
FETCH NEXT FROM my_cursor INTO @.Description
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
What I am trying to do is replace the cursor used there by a single insert
statement such as:
CREATE PROCEDURE CopyTasksFromOrderToOrder (@.fromO int, @.toO int) AS
DECLARE
@.i int
-- Retrieve the currently maximum ID for the destination order
SELECT @.i = ISNULL(MAX(ID),0) FROM TTasks WHERE OrderID = @.toO
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO, ****, Description FROM TTasks
WHERE TTasks.OrderID = @.fromO
ORDER BY TTasks.ID
GO
What is driving me crazy is how to calculate the field marked with ****
Can someone help me? Is there any way to do it without having ID field in
TTasks being an identity? It cannot be an identity since it must start with
1 for every new order.
Thanks in advance.jagb (jagb@.NOSPAM.com) writes:
> What is driving me crazy is how to calculate the field marked with ****
> Can someone help me? Is there any way to do it without having ID field
> in TTasks being an identity? It cannot be an identity since it must
> start with 1 for every new order.
A very simple-minded solution is to bounce the data over a temp table
with an IDENTITY column.
A more "relational" solution is to add a table of numbers to the database.
This is a one-column table that holds all numbers from 1 up to some limit.
Personally, I sort of favour the temp-table solution, as it is more
robust. You don't risk to run out of numbers. Then again, there is a
performance cost for using an extra table, so for this case I might go
for a table of numbers.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could use a correlated subquery to generate the ranking value within a
SELECT statement like:
INSERT tasks ( ... )
SELECT @.to, ( SELECT COUNT(*)
FROM tasks t2
WHERE t2.orderID = t1.Orderid
AND t2.id <= t1.id )
FROM tasks t1
WHERE t1.orderid = @.p ;
The assumption is that the id values are unique for each Orderid. This is
untested, if you'd like a tested one, please post a few sample data along
with expected results.
Anith|||TOrders (ID, Desc)
1 First Order
2 Second Order
TTasks (OrderID ID Descripcion)
1 1 a
1 2 b
1 9 c
2 1 WW
2 2 XX
2 3 YY
2 4 ZZ
After having run the SP to copy tasks from order=1 to order=2 the tasks
table should contain the following values:
TTasks (OrderID ID Descripcion)
1 1 a
1 2 b
1 9 c
2 1 WW
2 2 XX
2 3 YY
2 4 ZZ
2 5 a <-- these are the inserted records
2 6 b <-- these are the inserted records
2 7 c <-- these are the inserted records
This is not achieved using your statement wich will incorrectly try to
insert the values:
2 1 a
2 2 b
2 9 c
And will raise a primary key conflict, since the keys (OrderID ID) valued
to (2 1), (2 2) and (2 3) already exist in TTasks.
Note that the (1 9 c) record should be converted to (2 7 c) when
copied along for order 2.
Mhh... it seems that your approach almost hit in the nail... after having
done some tests I have found the solution:
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO,
(SELECT ISNULL(COUNT(*), 0)
FROM dbo.TTasks t2
WHERE (OrderID = t1.OrderID) AND (ID <=
t1.ID)) +
(SELECT ISNULL(MAX(ID), 0)
FROM dbo.TTasks
WHERE (OrderID = @.toO)), Descripcion
FROM dbo.TTasks t1
WHERE (OrderID = @.fromO)
Thanks for your help, Anith.
"Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
news:eZf8nlfEGHA.2380@.TK2MSFTNGP12.phx.gbl...
> You could use a correlated subquery to generate the ranking value within a
> SELECT statement like:
> INSERT tasks ( ... )
> SELECT @.to, ( SELECT COUNT(*)
> FROM tasks t2
> WHERE t2.orderID = t1.Orderid
> AND t2.id <= t1.id )
> FROM tasks t1
> WHERE t1.orderid = @.p ;
> The assumption is that the id values are unique for each Orderid. This is
> untested, if you'd like a tested one, please post a few sample data along
> with expected results.
> --
> Anith
>|||jagb (jagb@.NOSPAM.com) writes:
> Mhh... it seems that your approach almost hit in the nail... after having
> done some tests I have found the solution:
> INSERT INTO TTasks (OrderID, ID, Description)
> SELECT @.toO,
> (SELECT ISNULL(COUNT(*), 0)
> FROM dbo.TTasks t2
> WHERE (OrderID = t1.OrderID) AND (ID <=
> t1.ID)) +
> (SELECT ISNULL(MAX(ID), 0)
> FROM dbo.TTasks
> WHERE (OrderID = @.toO)), Descripcion
> FROM dbo.TTasks t1
> WHERE (OrderID = @.fromO)
>
> Thanks for your help, Anith.
Beware, though, that nested subqueries in the SELECT list often gives
poor performance. Certinly better than your cursor, it can be considerably
slower than bouncing over a temp table, or using a table of numbers.
Then again, it depends on how many rows you insert at time. If it is
< 100, the difference may not be measurable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You can also use a Table Variable with an IDENTITY column. I like them
better than temp tables for generating row numbers because unlike temp
tables, modifications to table variables don't share the same transaction
space as changes to permanent tables which can reduce the performance hit of
using the extra table. They also reduce recompiles and can reduce
contention on the system table indexes in tempdb.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97428F2528D48Yazorman@.127.0.0.1...
> jagb (jagb@.NOSPAM.com) writes:
> A very simple-minded solution is to bounce the data over a temp table
> with an IDENTITY column.
> A more "relational" solution is to add a table of numbers to the database.
> This is a one-column table that holds all numbers from 1 up to some limit.
> Personally, I sort of favour the temp-table solution, as it is more
> robust. You don't risk to run out of numbers. Then again, there is a
> performance cost for using an extra table, so for this case I might go
> for a table of numbers.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||I think bouncing the values off a temporary object will be faster than a
correlated subquery:
DECLARE @.T TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Description VARCHAR(64) NOT NULL
)
BEGIN TRAN
INSERT @.T (Description)
SELECT Description
FROM TTasks WITH(UPDLOCK, HOLDLOCK) --block updates to source order
WHERE OrderID = @.fromO
ORDER BY ID
IF @.@.ROWCOUNT > 0
BEGIN
SELECT @.i = ISNULL(MAX(ID), 0)
FROM TTasks WITH(UPDLOCK, HOLDLOCK) --block appending to destination
order
WHERE OrderID = @.to0
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO, ID + @.i, Description FROM @.T
END
COMMIT TRAN
"jagb" <jagb@.NOSPAM.com> wrote in message
news:esRjDsgEGHA.2704@.TK2MSFTNGP15.phx.gbl...
> TOrders (ID, Desc)
> 1 First Order
> 2 Second Order
>
> TTasks (OrderID ID Descripcion)
> 1 1 a
> 1 2 b
> 1 9 c
> 2 1 WW
> 2 2 XX
> 2 3 YY
> 2 4 ZZ
> After having run the SP to copy tasks from order=1 to order=2 the tasks
> table should contain the following values:
> TTasks (OrderID ID Descripcion)
> 1 1 a
> 1 2 b
> 1 9 c
> 2 1 WW
> 2 2 XX
> 2 3 YY
> 2 4 ZZ
> 2 5 a <-- these are the inserted records
> 2 6 b <-- these are the inserted records
> 2 7 c <-- these are the inserted records
> This is not achieved using your statement wich will incorrectly try to
> insert the values:
> 2 1 a
> 2 2 b
> 2 9 c
> And will raise a primary key conflict, since the keys (OrderID ID) valued
> to (2 1), (2 2) and (2 3) already exist in TTasks.
> Note that the (1 9 c) record should be converted to (2 7 c)
> when copied along for order 2.
> Mhh... it seems that your approach almost hit in the nail... after having
> done some tests I have found the solution:
> INSERT INTO TTasks (OrderID, ID, Description)
> SELECT @.toO,
> (SELECT ISNULL(COUNT(*), 0)
> FROM dbo.TTasks t2
> WHERE (OrderID = t1.OrderID) AND (ID <=
> t1.ID)) +
> (SELECT ISNULL(MAX(ID), 0)
> FROM dbo.TTasks
> WHERE (OrderID = @.toO)), Descripcion
> FROM dbo.TTasks t1
> WHERE (OrderID = @.fromO)
>
> Thanks for your help, Anith.
> "Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
> news:eZf8nlfEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>

avoid using cursors

Hi All,
I want to avoid using cursors and loops in stored procedures.
Please suggest alternate solutions with example (if possible).

Any suggestion in these regards will be appreciated.

Thanks in advance,
T.S.NegiHere is a sample:
http://www.extremeexperts.com/SQL/A...TSQLResult.aspx

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

"T.S.Negi" <tilak.negi@.mind-infotech.com> wrote in message
news:a1930058.0502132109.268ae0dd@.posting.google.c om...
> Hi All,
> I want to avoid using cursors and loops in stored procedures.
> Please suggest alternate solutions with example (if possible).
> Any suggestion in these regards will be appreciated.
>
> Thanks in advance,
> T.S.Negi|||Loops and cursors are just programming constructs, not actual problems
to be solved, so there is no generic solution or example to show how to
avoid them. You should try to avoid or minimize the use of loops and
cursors in SQL and this is done by writing standard set-based code:
SELECT, UPDATE, DELETE and INSERT statements that operate on sets of
rows rather than one row at a time. The actual details will depend on
exactly what you want to achieve.

If you require hlep with a specific problem then please post more
details as described in:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||Unfortunately, all the examples in that article are loops!

--
David Portas
SQL Server MVP
--|||tilak.negi@.mind-infotech.com (T.S.Negi) wrote:

>Hi All,
>I want to avoid using cursors and loops in stored procedures.
>Please suggest alternate solutions with example (if possible).

Off at a tangent:

I see many times in these groups (and elsewhere) that cursors should
be avoided. As a comparative newbie can someone explain to me the
reasons for this.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||There are plenty of good reasons to use declarative, set-based SQL code
instead of cursors. The reason most usually given is performance. SQL
Server, like other SQL databases, is designed primarily for
set-at-a-time rather than row-at-a-time operations. Cursors are
typically very slow, although performance obviously varies considerably
depending on what you are doing. Paradoxically, it is also true to say
that there is a small class of problems for which cursors are faster
than any set-based solution. In a well-designed database those
situations are uncommon in my experience and you would be well-advised
to get a second opinion if you think you have come across such a case.
Performance and locking issues with cursors tend to mean they are far
less scalable than the set-based alternatives so even if they work for
you today they may not be a viable solution in future.

Besides performance there are other good reasons to use set-based code:
The declarative code is usually much more concise and therefore easier
to develop, inspect, test and maintain; It's more likely to be
portable to other database platforms; SQL professionals (good ones
using TSQL anyway) tend to write cursors seldom and so are likely to be
more comfortable and more productive writing set-based code; Set-based
code avoids or tends to show-up some of the logical anomalies and
design problems that can lie hidden and unnoticed in procedural cursor
code.

A legitimate place to use a cursor is for something inherently
procedural (typically admin tasks such as managing backups, sending
emails or importing/exporting files) but in general in an RDBMS you
should assume the solution to any data-manipulation problem will be
set-based unless expert analysis proves otherwise. That's why I would
class cursors as a feature for advanced users only. If you find
yourself writing cursors regularly then it's time to rethink what you
are doing or maybe go on a course to learn grown-up SQL (too many
cursors are written by programmers who don't know better techniques)
:-)

--
David Portas
SQL Server MVP
--|||On Tue, 15 Feb 2005 14:21:03 +0000, John A Fotheringham wrote:

>I see many times in these groups (and elsewhere) that cursors should
>be avoided. As a comparative newbie can someone explain to me the
>reasons for this.

Hi John,

SQL Server is heavily optimized towards set-based operations, where you
use one single query to specify what you want and let SQL Server work out
the best strategy to satisfy your request. That's why SQL is called a
declarative language (you declare the intended results, not the way to get
there, as opposed to procedural languages (where you specify the procedure
to get the intended results).

Using cursors is forcing a procedural approach on SQL. You still use a
query to specify the rows you want to fetch (the declarative part), but
then you fetch one, do something with it and fetch the next - that is
purely procedural.

It is my experience that at least 99% of all existing cursor-based code
can be replaced by set-based code. In most cases, the replacing set-based
code is shorter (less lines), easier to read, understand and maintain (at
least after you've mastered the learning curve to switch from procedural
thinking to declarative thinking) and -most important- performs much
faster.

For the remaining less than one percent, cursors are indeed the best
solution. I won't say that cursors should _always_ be avoided. But I will
say that they are to be used as a final resort only - and it's always wise
to get a second opinion first. Newsgroups are a great place to explain
your problem and ask if others agree that this particular problem can't be
solved with declarative code. In most cases, you'll get a surprising
answer - and if you take the trouble to not only copy and adapt the code
posted, but also to try to understand it, you'll get a great learning
experience thrown in for free!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||One trick I have started to use:
Whenever I need to iterate in a procedure I have a table that I have
created with only one column like so:

CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Declare @.i as int
set @.i = 0
while @.i <= 10000
begin
Insert into dbo.Numbers Default values
set @.i = @.i + 1
end

GO

Now with this table I can query against it like it were a loop but
still remaining set based.
So for instance If I were trying to Schedule a date every 7 days for
the next year I could do something like

insert into dates(dates)
Select DateAdd(Day,pk_Number * 7,getdate())
from Numbers
where pk_number < 366

Just a little trick

Tal McMahon|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> It is my experience that at least 99% of all existing cursor-based code
> can be replaced by set-based code. In most cases, the replacing set-based
> code is shorter (less lines), easier to read, understand and maintain (at
> least after you've mastered the learning curve to switch from procedural
> thinking to declarative thinking) and -most important- performs much
> faster.

Our system has its fair share ot iterative processing, and maybe the
most common good reason to use a cursor is that you have a stored
procedure that performs an operation on a single set of values and
you want to reuse that logic.

If all the procedure performs is a simple update, or a plain insert,
there's little reason to keep the procedure.

But we have core procedures that performs a lot of updates and inserts
(including validations) for a bunch of in-parameters. Rewriting such
a procedure to operate set-based from an input table is a major task.
We've done it in one case. I seem to recall that the time estimate was
200 h, and I think we exceeded that. The result is a monster procedure
on 3000 lines that uses 43 table variables.

This particular rewrite was necessary given some of the volumes that can
occur in some of the iterations with its predecessor. But I can tell
you that I am not going to initiate more rewrites, just for the sake
of it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 15 Feb 2005 22:57:01 +0000 (UTC), Erland Sommarskog wrote:

(snip)
>This particular rewrite was necessary given some of the volumes that can
>occur in some of the iterations with its predecessor. But I can tell
>you that I am not going to initiate more rewrites, just for the sake
>of it.

Hi Erland,

I see what you mean and I totally agree: if it works and it is not "too"
slow, then there is abolutely no reason to change it.

The often prohibitive cost of rewrites is just another reason for me to
continue trying to convince everybody to write set-based code right from
the off.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have a book called SQL PROGRAMMING STYLE that should be published by
2005 April that has some chapters on how to think in Sets instead of
procedures. There is no single, magic answer.|||>> I see many times in these groups (and elsewhere) that cursors should
be avoided. As a comparative newbie can someone explain to me the
reasons for this. <<

All of the performance reasons that portas, Kornelis and Sommarskog
gave are the usual reasons. But don't for get the lack of portability!

In spite of the SQL standards, cursors are still VERY proprietary. But
even if they were all perfectly aligned, there are enough
"implementation dependent" things to screw you over. For example,
there is a warning that is raised when a GROUP BY has a NULL removed
from one of the groups. Sounds good, since I might not want to run a
report if I have missing data in one or more groups ("And where the
hell is Smith's sales figures??!")

But this warning can be raised at DECLARE CURSOR, OPEN cursor and/or
FETCH cursor. This is going to change application program logic quite
a bit.|||Except, of course, that this schedules the event every 7 days for the
next 7 years...

Your where clause should be:
where (pk_number * 7) < 366|||Erland Sommarskog <esquel@.sommarskog.se> wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> It is my experience that at least 99% of all existing cursor-based code
>> can be replaced by set-based code. In most cases, the replacing set-based
>> code is shorter (less lines), easier to read, understand and maintain (at
>> least after you've mastered the learning curve to switch from procedural
>> thinking to declarative thinking) and -most important- performs much
>> faster.
>Our system has its fair share ot iterative processing, and maybe the
>most common good reason to use a cursor is that you have a stored
>procedure that performs an operation on a single set of values and
>you want to reuse that logic.

This is the only situation in which I've used a cursor so far.

I have a trigger set on insert into one table, and for each inserted
record I want to use it's contents to create and/or update the
contents of a record in a second table.

I've written a procedure to do the fairly complex update from one
record to another, and I call that procedure from inside a fairly
simple cursor loop that forms the main body of the trigger procedure.

I'm not too worried about cursor overheads here, because in general
only one record at a time is being inserted into the first table.

At the time I wrote this cursors seemed the only (and natural) way to
iterate through the records in the "inserted" table, which in this
instance is what I need to do as each record has to be processed
separately.

If there's a better/more appropriate way of doing this I'd be
interested to hear it. A lot of the "set-based" solutions I see here
just seem to be implementing loops using a table and from a purely
programming point of view (my background) seem a counter-intuitive way
of doing things.

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||I would recommend that you don't use cursors in triggers. As already
discussed, if you have a legacy of procedural code that works on one
row at a time then yes, you may be forced to call that code in a loop
just because of the cost of rewriting your procedure in set-based form.
That's a pity because some day you may want to update more than one row
at a time and anyway you really don't need the overhead of a cursor
declaration in a trigger, even for a single row.

> A lot of the "set-based" solutions I see here
> just seem to be implementing loops using a table and from a purely
> programming point of view (my background) seem a counter-intuitive
way
> of doing things.

I don't know what you are referring to. Maybe you have an example? If
you mean using WHILE loops and SELECT statements in place of cursors
then yes, that is just cursor in disguise and all my comments about
cursors apply equally to those other row-by-row constructs.

It is true that declarative SQL requires a slightly different mindset
and it is often noted that procedural programmers find these methods
counter-intuitive. However, the relational model and SQL are the
dominant industry database standards with good reason and that is
hopefully a good enough incentive for the programmer to learn the
standard techniques and best-practices, aside from the very practical
considerations already explained.

--
David Portas
SQL Server MVP
--|||> If there's a better/more appropriate way of doing this I'd be
> interested to hear it.

Very likely there is a better way but we'll need more details first:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>I would recommend that you don't use cursors in triggers. As already
>discussed, if you have a legacy of procedural code that works on one
>row at a time then yes, you may be forced to call that code in a loop
>just because of the cost of rewriting your procedure in set-based form.
>That's a pity because some day you may want to update more than one row
>at a time and anyway you really don't need the overhead of a cursor
>declaration in a trigger, even for a single row.

So how would you advise to select a single row from the inserted table
without using a cursor?

>> A lot of the "set-based" solutions I see here
>> just seem to be implementing loops using a table and from a purely
>> programming point of view (my background) seem a counter-intuitive
>way
>> of doing things.
>I don't know what you are referring to. Maybe you have an example? If
>you mean using WHILE loops and SELECT statements in place of cursors
>then yes, that is just cursor in disguise and all my comments about
>cursors apply equally to those other row-by-row constructs.

Well that's what I thought. Most of these approaches seem to be a
loop through table, and it's not obvious (to the newbies) that this
would be more efficient than a cursor.

>It is true that declarative SQL requires a slightly different mindset
>and it is often noted that procedural programmers find these methods
>counter-intuitive. However, the relational model and SQL are the
>dominant industry database standards with good reason and that is
>hopefully a good enough incentive for the programmer to learn the
>standard techniques and best-practices, aside from the very practical
>considerations already explained.

I'm not disputing this, just asking questions so I can better get into
the correct mindset for SQL.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>> If there's a better/more appropriate way of doing this I'd be
>> interested to hear it.
>Very likely there is a better way but we'll need more details first:
>http://www.aspfaq.com/etiquette.asp?id=5006

Well I was really just asking a generic question. The tables involved
are fairly lengthy (although as far as the logic goes only a few
fields apply) and I didn't think to fill my post with all the details.
I wasn't seeking a particular solution, just asking the general
question.

In essence I have

CREATE TABLE transaction
(
IDint IDENTITY(1,1),

tsdatetime
jobvarchar(20)
statuschar(1)
...
other transaction fields
...
)

CREATE TABLE jobs
(
jobvarchar(20)
statuschar(1)
last_updatedatetime
...
other job fields
...
)

Each time a transaction comes in I use the details in the transaction
to update the jobs table. If it's a new job I create a new record,
otherwise I perform an update. The nature of the update can depend
on the value of the new status, so that depending on the status
different values amongst the "other transaction fields" will cause
different updated for the "other job fields". Further processing may
occur for some status codes.

To achieve this I wrote a "processTrn" procedure which takes a single
transaction and executes all the (largely procedural) updates.

To call this procedure I created a trigger on the transactions table,
and it's there that I use a cursor to go through the "inserted" table
to extract each new record in turn and call the procedure on it.

To my mind this is a naturally loop+procedural process.

Note, because the transactions table is added to 1 record at a time by
an external process, the actual cursor loops in this case are usually
for a single record.

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||> So how would you advise to select a single row from the inserted
table
> without using a cursor?

I *wouldn't* select a single row. The problem is precisely to AVOID
processing single rows of data and process the whole set of data at
once. This is what we mean by "set-based" code. By putting business
logic in a stored proc that opeartes only on one row at a time you have
forced yourself to call that proc once for each row. It likely doesn't
have to be that way but since you haven't explained what the proc does
I can't really advise on the alternatives.

--
David Portas
SQL Server MVP
--|||> If it's a new job I create a new record

INSERT INTO Jobs (job, ...)
SELECT job, ...
FROM Inserted
WHERE NOT EXISTS
(SELECT *
FROM Jobs
WHERE job = Inserted.job)

Note however that you should generally avoid duplicating data between
tables (except for key columns). Duplicated data is a problem in a
relational database and the goal of Normalization in db design is to
eliminate it. You should also aim to eliminate transitive dependencies
- i.e. columns that can always be derived from data in other (non-key
columns) - doing so reduces the need for triggers.

> otherwise I perform an update

UPDATE Jobs
SET ... ?
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE job = Jobs.job AND ... ?)

> The nature of the update can depend
> on the value of the new status, so that depending on the status
> different values amongst the "other transaction fields" will cause
> different updated for the "other job fields".

That's not much information to go on but you could probably use CASE
expressions for this.

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>> If it's a new job I create a new record
>INSERT INTO Jobs (job, ...)
> SELECT job, ...
> FROM Inserted
> WHERE NOT EXISTS
> (SELECT *
> FROM Jobs
> WHERE job = Inserted.job)

Thanks. I begin to see how the procedural approach can be avoided.

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||LOL,
yeah I guess you are right must have been late.|||John A Fotheringham (jafsoft@.gmail.com) writes:
> This is the only situation in which I've used a cursor so far.
> I have a trigger set on insert into one table, and for each inserted
> record I want to use it's contents to create and/or update the
> contents of a record in a second table.
> I've written a procedure to do the fairly complex update from one
> record to another, and I call that procedure from inside a fairly
> simple cursor loop that forms the main body of the trigger procedure.
> I'm not too worried about cursor overheads here, because in general
> only one record at a time is being inserted into the first table.

Normally, it is not a good idea ot have a cursor in a trigger, but if
you know your business well enough to be confident that one row-at-a-time
is the normal case, this sounds like a sound approach to me. From a
theoretical point of view, the trigger certainly could be improved. But as
long as the penalty for the cursor is low or non-existent, it seems very
difficult to justify spending time on a more complex solution.

That cannot be denied, if you want to encapsulate logic by putting
it in stored procedures, this is easier for scalar values than for
sets of values, since procedure parameters are scalar. It is possible
to work around this by sharing temp tables or similar, but only does
this increase complexity. You can also get recompilation issues that
are bad for performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Avoid Looping / Cursors. Help with Statement.

Hi Folks,
I have two tables, one of which I want to update from another.
Essentiall I have a table of orders and a product table. I want to
subtract the qty sold in the orders table from the QtyInStock column in
the Products table, for every line in an order.
But I dont really want to loop through each line in the order, either
in application or with a cursor as something is telling me there must
be a neater solution!
Example Orders Table.
OrderID ProductID Qty
1, 104, 2
1, 199, 1
2, 100, 3
3, 858, 1
ProductID, QtyInStock
104, 3
199, 1
etc ...
As you can see I want to be able to run a query against OrderID 1,
and it to reduce the QtyInStock column by the correct amount for
products 104 and 199 as an example.
Can this be done with one statement, or will I have to loop ?
Thanks in Advance.
Craig.Hi
CREATE TABLE #Test1
(
OrderID int,
ProductID int,
Qty int
)
INSERT INTO #Test1 VALUES (1,104,2)
INSERT INTO #Test1 VALUES (1,199,1)
INSERT INTO #Test1 VALUES (2,100,3)
INSERT INTO #Test1 VALUES (3,828,1)
CREATE TABLE #Test2
(
ProductID int,
QtyInStock int
)
INSERT INTO #Test2 VALUES (104,3)
INSERT INTO #Test2 VALUES (199,1)
UPDATE #Test1 SET Qty=(SELECT QtyInStock-Qty
FROM #Test2 T WHERE T.ProductID=#Test1.ProductID)
WHERE EXISTS (SELECT * FROM #Test2
T WHERE T.ProductID=#Test1.ProductID)
DROP TABLE #Test1,#Test2
<craig.parsons@.crawfos.com> wrote in message
news:1136476091.334221.282250@.g44g2000cwa.googlegroups.com...
> Hi Folks,
> I have two tables, one of which I want to update from another.
> Essentiall I have a table of orders and a product table. I want to
> subtract the qty sold in the orders table from the QtyInStock column in
> the Products table, for every line in an order.
> But I dont really want to loop through each line in the order, either
> in application or with a cursor as something is telling me there must
> be a neater solution!
> Example Orders Table.
> OrderID ProductID Qty
> 1, 104, 2
> 1, 199, 1
> 2, 100, 3
> 3, 858, 1
> ProductID, QtyInStock
> 104, 3
> 199, 1
> etc ...
> As you can see I want to be able to run a query against OrderID 1,
> and it to reduce the QtyInStock column by the correct amount for
> products 104 and 199 as an example.
> Can this be done with one statement, or will I have to loop ?
>
> Thanks in Advance.
>
> Craig.
>|||Uri,
I think Craig wants to update the quantity in stock
from the Product table, not the quantity in the Orders
table, which your query updates. He could use Jens's
solution, or one like this:
UPDATE #Products SET
QtyInStock = QtyInStock - (
SELECT SUM(O.Qty)
FROM #Orders AS O
WHERE O.ProductID = #Products.ProductID
)
WHERE EXISTS (
SELECT * FROM #Orders
WHERE #Orders.ProductID = #Products.ProductID
)
Steve Kass
Drew University
Uri Dimant wrote:

>Hi
>CREATE TABLE #Test1
>(
> OrderID int,
> ProductID int,
> Qty int
> )
>INSERT INTO #Test1 VALUES (1,104,2)
>INSERT INTO #Test1 VALUES (1,199,1)
>INSERT INTO #Test1 VALUES (2,100,3)
>INSERT INTO #Test1 VALUES (3,828,1)
>
>CREATE TABLE #Test2
>(
> ProductID int,
> QtyInStock int
> )
>INSERT INTO #Test2 VALUES (104,3)
>INSERT INTO #Test2 VALUES (199,1)
>
>UPDATE #Test1 SET Qty=(SELECT QtyInStock-Qty
>FROM #Test2 T WHERE T.ProductID=#Test1.ProductID)
>WHERE EXISTS (SELECT * FROM #Test2
>T WHERE T.ProductID=#Test1.ProductID)
>
>
>DROP TABLE #Test1,#Test2
>
>
><craig.parsons@.crawfos.com> wrote in message
>news:1136476091.334221.282250@.g44g2000cwa.googlegroups.com...
>
>
>|||craig.parsons@.crawfos.com wrote:
> Hi Folks,
> I have two tables, one of which I want to update from another.
> Essentiall I have a table of orders and a product table. I want to
> subtract the qty sold in the orders table from the QtyInStock column
> in the Products table, for every line in an order.
> But I dont really want to loop through each line in the order,
> either in application or with a cursor as something is telling me
> there must be a neater solution!
> Example Orders Table.
> OrderID ProductID Qty
> 1, 104, 2
> 1, 199, 1
> 2, 100, 3
> 3, 858, 1
> ProductID, QtyInStock
> 104, 3
> 199, 1
> etc ...
> As you can see I want to be able to run a query against OrderID 1,
> and it to reduce the QtyInStock column by the correct amount for
> products 104 and 199 as an example.
> Can this be done with one statement, or will I have to loop ?
>
Here is the ANSI version (I used the Sum function to guarantee only a single
result would be returned):
UPDATE Products
SET QtyInStock = QtyInStock -
(SELECT Sum(Qty) FROM Orders o
WHERE o.OrderID = 1 AND o.ProductID = Products.ProductID)
The T-SQL version:
UPDATE p
SET QtyInStock = QtyInStock - o.Qty
FROM Products p inner join (
SELECT ProductID,Sum(Qty) AS Qty FROM Orders
WHERE OrderID = 1 GROUP BY ProductID) o
ON o.ProductID = p.ProductID
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

avoid cursors.

hi what are ways of avoiding cursors?
my boss told me to use derived tables. any other tricks i can use?"ichor" <ichor@.hotmail.com> wrote in message
news:OyNAbw33FHA.3592@.TK2MSFTNGP12.phx.gbl...
> hi what are ways of avoiding cursors?
> my boss told me to use derived tables. any other tricks i can use?
>
It's usually a good idea to avoid cursors and mostly they are unnecessary.
For most data manipulation problems you can achieve the same thing as a
cursor by using set based code (code that operates on the entire set of data
at once rather than one row at a time). There is no single set based
technique to replace a cursor because cursors don't represent a particular
class of problem, they are just one tool with which to solve problems.
David Portas
SQL Server MVP
--|||Read up in SQL Server Books Online on topics like temporary tables,
subqueries, and use of the Case function. These can be used to replace
cursor based programming that performs complex row selection or updates.
"ichor" <ichor@.hotmail.com> wrote in message
news:OyNAbw33FHA.3592@.TK2MSFTNGP12.phx.gbl...
> hi what are ways of avoiding cursors?
> my boss told me to use derived tables. any other tricks i can use?
>