Re: Avoide cursor in Tsql
It would be better if you provide more details...
Have you tried temporary tables?
Re: Avoide cursor in Tsql
how do you loop a temporary table ?
Re: Avoide cursor in Tsql
Re: Avoide cursor in Tsql
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)
Code:
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
Re: Avoide cursor in Tsql
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:-
Code:
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.
Re: Avoide cursor in Tsql
Quote:
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