Avoide cursor in Tsql

[wiss.dev]

im writing a stored procedure, trying to avoid the use of a cursor but cant think of a work around. here is a sample example of my case.
i have tables : customers and orders
first i query the orders for any lines with due quantity grouped by the customer id into a cursor.
then i loop the cursor and select all due orders for each customer into a second cursor which i loop in turn to create a html file in a specific format which i then email through.

i couldnt come with a more efficient structure, any suggestions ??
thanks

[jggtz]

It would be better if you provide more details...

Have you tried temporary tables?

[wiss.dev]

how do you loop a temporary table ?

[jggtz]

Apply another SELECT

[techgnome]

This kind of stuff just happens to be right up my wheelhouse...

Here's an example of how you can accomplish this w/o a cursor... it still involves looping... but at least it won't be a cursor.
(since I don't know your exact fields, I'm jsut giving an EXAMPLE... you'll need to expand it for your own)

declare @tmpCustomers table (SEQUENCE int, ID int, CustomName varchar(50), ...)

declare @tmpOrders table (SEQUENCE int, ID int, CustomID int, OrderDate date, ...)

insert into @tmpCustomers (SEQUENCE, ID, Name, ...)
select row_number() over (order by ID), ID, Name from Customer where .... put your criteria here

insert into @tmpOrders (SEQUENCE, ID, CustomerID, OrderDate, ...)
select row_number() over (partition by O.CustomerID order by O.CustomerID, O.Id), O.Id, O.CustomerID, O.OrderDate from Orders O inner join @tmpCustomer C on O.CustomerID = C.ID

declare @RowNum int = 1

while exists (select SEQUENCE from @tmpCustomers where SEQUENCE = @RowNum)
  begin

    -- Use the @RowNum to select your data from the customers temp table, that will give you the customer ID
    -- Using the Customer ID, you can select from the orders temp table the info to create your html files as needed

    select @RowNum = Min(SEQUENCE) from @tmpCustomer -- this gets the next number in the sequence

  end

No cursors.


Documentation for row_number - http://msdn.microsoft.com/en-us/library/ms186734.aspx ... it does say SQL Server 2012 initially, but this function goes back to SQL 2005 ...

-tg

[FunkyDexter]

Personally I'm not convinced a while loop is significantly better than a cursor. You'll use slightly less resources but it's still RBAR and a decent set based aproach will almost certainly give much better performance and hog less resource.

wiss.dev, I'm not sure I'm understanding your problem properly so maybe post the cursor you've got. It will make it easier to understand what you're trying to do. Here's a quick punt from what I think you're after though:-

select * 
From Customers C
Join Orders O
	on C.CustomerNumber = O.CustomerNumber
Where Orders.DueQuantity > 0
Order By C.CustomerNumber

That will give you all customers who have an order with a due quantity along with those orders. I've used an order by because you may still have to loop through the set to create and send the html doc, in which case you're going to want them coming out in "blocks". You probably don't need to do that looping, though, and if we can see your cursor we can probably eliminate that as well.

[techgnome]

Personally I'm not convinced a while loop is significantly better than a cursor

I've done EXTENSIVE testing on this ... a cursor is BY FAR the slowest kind of looping. There is an amazing amount of overhead on those things... plus you have the potential to run into locks and it could cause deadlocking.

In a prior life we did a lot of experimenting and testing on loops as our system did a lot of it. We found that cursors were the slowest form of looping. By dumping the data into temp tables with a sequence number, and looping through them that way... blazingly fast comparatively. Might have also something to do with dealing with tens of thousands of rows at a time. Sure, we would do set-based operations when we could, but there were some times when it just wasn't possible.

So even now, I'll do set-based operations, if for what ever reason I can't, I fall back on a while loop with a temp table, and if absolutely no choice, I use the cursor in last-resort scenarios.

-tg