November 4, 2005 at 1:44 pm
In VB in I can loop thru a tables records and use the data in each field via a recordset.
In SQL I can do the same thing via a CURSOR, but I understand that this is not best practise.
What other methods can I use that have the same power as a recordset
WHILE loops, but how ? SELECT Statements ???
I know I can place data in TEMP tables, but I need to loop thru all fields in the table, I do not want to do heaps of SELECT statements on each field for the one table.
Eg vb
Set rs = Database.openrecordset("SELECT * FROM ;")
Do Until rs.EOF
If rs.field1 > 0 Then
etc, etc, etc
end if
If rs.field2 > 0 Then
etc, etc, etc
end if
rs.movenext
Loop
Please advise ?
November 4, 2005 at 2:20 pm
I found this use of a WHILE clause, what do you think ?( By: ikramkahn )
DECALRE @somevar varchar(30) --optional filter paramter
DECALRE @maxrow int
DECALRE @ii int
DECALRE @param1 int
DECALRE @param2 varchar(50)
DECALRE @tablevar table (
IDENTITY(int, 1,1) AS TID,
Param1 int,
Param2 varchar(50))
INSERT @tablevar
SELECT ColA,ColB from sometable where whatever = @somevar
SET @maxrow=@@rowcount
SET @ii =1
WHILE @ii<= @maxrow
BEGIN
---insert your desired code here or for example
SELECT @param1 = Param1, @param2=Param2 FROM @tablevar WHERE tid = @ii
UPDATE someothertable
SET somefield = @param2
WHERE anotherfield = @param1
SET @ii = @ii +1
END
/*
Please note that for very large result sets it is better to create a temp table than to use table variables.
I sincerely hope that this is of some use to someone as it has helped me tremendously
*/
November 5, 2005 at 6:40 am
Also have a look at the CASE statement (acts like an if).
Also consider splitting up your processing to set-based solutions.
example:
SELECT 'ABC' WHERE myfield1='A'
SELECT 'DEF' WHERE myfield2 ='B'
SELECT
CASE myfield1='A' THEN 'ABC'
CASE myfield2='B' THEN 'DEF'
END AS RESULT
Can you give an example of your recordset processing?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply