in Vb(DAO. AD) you have a recordset, what to do in SQL ?

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

  • 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

    */

     

     

  • 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