while loop cicle (i=i+1) insted of using cursor
-
Hi....
Insted of using cursor I'd like to use a "while" loop ..and retrieve a data from a query
e.g. a table: products
field: numbers product
row1 10 a
r0w2 5 b
declare @i int
declare @rowcount int
declare @result char (50)
set @i = 1
set @rowcount = (select count(*) from products) -- in this case @rowcount = 2
While @i <= @rowcount
begin
set @result = (select product from products) -- my problem is: how can I scroll row by row and retrieve every product?
set @i = @i +1
end
My problem is to retrieve a record row by row .......I mean in this case I should retrieve :
a
b
Thank to everybody for your useful help...........
-
Is there something you're leaving out of your question ?!
Why can you not just do a "select product from products" ??
**ASCII stupid question, get a stupid ANSI !!!** -
.......because I have to retrieve a record for every row in the table and then re-used in a string to execute a batch command......
waiting your suggestions.........
-
I can't see any advantage this form of access would have over a cursor. Cursors are slower than set operations, but I'd bet this is one case where the cursor would be faster than issuing a select for every row.
You could issue a select that would find the next row by:
select a.product from products a where a.product = (select min(b.product) from products b where b.product > @result)
If someone adds or deletes a row while this process is going on, you'll end up with NULL where you expect data or miss data you should pick up.
And then again, I might be wrong ...
David Webb -
Is this what you are looking for?
Declare @temp table
(
row_id int identity (1,1),
product varchar(50)
)
Declare @i int,
@max-2 int
Insert into @temp (product)
Select product
From products
set @max-2 = @@rowcount
set @i = 1
While @i <= @max-2
Begin
Select
product
From
@temp
Where row_id = @i
--- do your processing
set @i = @i + 1
End
-
I have seen code like this in SQL Server 6.5 by some developers.
DECLARE @product VARCHAR(50)
DECLARE @ProductID INT -- I assume Product ID as primary Key
SELECT *
INTO #Products
FROM
Products
WHILE EXISTS (SELECT * FROM #Products)
BEGIN
SET ROWCOUNT 1
SELECT @ProductID = ProductID,
@product = Product
FROM
#Products
SET ROWCOUNT 0
/*
Do your Processing here
*/
DELETE #Products WHERE ProductID = @ProductID
END
Regards,
gova -
Thank to everybody....
I'll test your code and find the right one for my problem......
-
erncelen - you may want to consider David's suggestion - I tried a cursor vs. a select from every row on a small table of 78 rows and even in that small a table, the select one took a second to run....
**ASCII stupid question, get a stupid ANSI !!!** -
David's suggestion uses min(). As the table becomes larger this gets to be more inefficient as more rows have to be evaluated.
Another variation on what I posted that should be better for a large table would be to use a #Table instead of a table variable and put an index on the row_id column.
Edit: The table variable has some advantages as it is created and held in memory.
Edit 2: I should have said that the use of Min() becomes more inefficient with table size if there is no usable index. With a usable index David's SELECT should work ok.
-
Ron - I was talking specifically about David's comment on "cursors" vs. "select from every row" - not his t-sql!
Also, I've never had to personally compare performance between #table and table variable but am under the impression that table variable is almost always the "way to go"...
**ASCII stupid question, get a stupid ANSI !!!** -
Builiding on David's suggestion:
Declare @product as varchar(50)
set @product = ''
While @product is not null
Begin
Select @product = min(product)
from products
Where product > @product
--
-- do your processing here
--
End
This assumes the column product is unique and if there are a large number of rows that there is an index on product.
The processing time on this method vs my earlier post using a table variable were very close on a 500 row table.
-
.....Back again......
thank to everybody,
I always used cursor, but recently I have realized to use While loop cicle just to see the difference and performance ....
Of course the use of index should be a right idea to get better performance.......
I 'll try to do some test and verify what solution is the best, depending case by case.......
Thank again............................
-
I'd stick with cursors. That's what they're designed for so I don't see the need to re-invent the wheel. Plus it's likely to be more efficient as others have pointed out.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply