Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2009
    Posts
    24

    Unanswered: So I decided to go with the cursor

    For some reason my cursor only reads one record, it does not loop (the TRL file contains 3 recs. Can someone tell me what I am missing?

    DECLARE @Run_Date as int
    DECLARE @DTL_CNT as int
    DECLARE @TRL_CNT as int
    DECLARE @File_Cnt as int
    DECLARE @Vend_Name as varchar(20)
    DECLARE @File_Name as varchar(255)
    DECLARE @TRL_Run_Date as int

    ---- *** XXX
    SET @Run_Date = Convert(Varchar(10),Getdate(),112)
    Print @Run_Date
    Set @File_Cnt = (Select count(*) FROM Trlr
    WHERE TRLR****nDate = @Run_Date And Trlr.Vendor_Name = 'XXX')
    PRINT @File_Cnt

    Declare vwCursor CURSOR FOR
    SELECT RunDate, Vendor_Name, Vendor_File_Name, Dtl_Count
    FROM Trlr
    WHERE TRLR****nDate = @Run_Date And Trlr.Vendor_Name = 'XXX'
    open vwCursor
    FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT

    SELECT A****nDate, A.Vendor_File_Name, A.Vendor_Name, count(*) as RecCnt
    into #tmp_recs_cnt_C
    FROM Dtl A
    Where A.Vendor_Name = 'XXX' and A.Vendor_File_Name = @File_Name
    AND A****nDate = @Run_Date
    group by A****nDate, A.Vendor_File_Name, A.Vendor_Name

    select Vendor_Name from Dtl where Vendor_File_Name = @File_Name
    select Vendor_Name from Dtl where Vendor_Name = 'XXX'
    select RUndate from Dtl where rundate = @Run_Date
    select * from #tmp_recs_cnt_C

    INSERT INTO Trlr_Errors (RunDate, Vendor_File_Name, Vendor_Name, dtl_count, T.RecCnt)
    SELECT A****nDate, A.Vendor_File_Name, A.Vendor_Name, A.dtl_count,T.RecCnt
    FROM Trlr A
    INNER JOIN #tmp_recs_cnt_C T
    ON A****nDate=T****nDate AND A.Vendor_File_Name=T.Vendor_File_Name AND A.Vendor_Name = T.Vendor_Name
    --AND A.dtl_count<>@TRL_CNT
    AND A.dtl_count=@TRL_CNT

    FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT
    CLOSE vwCursor
    DEALLOCATE vwCursor

    Thanx

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You forgot to program the loop.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE cursor_name CURSOR FOR
      SELECT statement
      ...
    
    
    FETCH NEXT FROM cursor_name INTO @variable_1, @variable_2
    
    WHILE @@Fetch_Status = 0
      BEGIN
        DO STUFF...
    
        FETCH NEXT FROM cursor_name INTO @variable_1, @variable_2    
      END
    
    CLOSE cursor_name
    DEALLOCATE cursor_name
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2009
    Posts
    24
    Thank you so much George for responding but I was talked into abandoning the Cursor.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yayy!
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Now if you would only give up crack and meth.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I know the best way to do things is set based. If you can't get it done that way you must rely on a procedural implementation. The only way I know of is with a cursor.

    But I have read that it is also possible to do it with a "WHILE" loop or something. But it was never clear to me how it works. Is it possible to show how it works in a similar way as the way George showed how to work with a cursor?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Wim
    I know the best way to do things is set based.
    Not always, but yes - almost without exception.

    Quote Originally Posted by Wim
    But I have read that it is also possible to do it with a "WHILE" loop or something. But it was never clear to me how it works. Is it possible to show how it works in a similar way as the way George showed how to work with a cursor?
    I only use WHILE loops - it saves me spending valuable time learning cursor syntax. I would only worry about cursors if I needed some sort of locking or other specialist setting that cursors can do for me.

    A crude example of a loop for something like this:
    Code:
    
    USE master
    GO
    
    DECLARE @db ASSYSNAME
    
    SELECT @db =''
    
    WHILE 1 = 1
    BEGIN
    
       SELECT @db =MIN(name)
       FROMsys.databases
       WHERE name > @db
    
       IF @db IS NULL BREAK
    
       PRINT @db
     
    END
    
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Couldn't that be streamlined a bit?
    Code:
    USE master
    GO
    
    DECLARE @db ASSYSNAME
    
    SET @db =''
    
    WHILE @db is not null
    BEGIN
    
       SELECT @db =MIN(name)
       FROM    sys.databases
       WHERE name > @db
    
       PRINT @db
     
    END
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Couldn't that be streamlined a bit?
    Yeah - it was quickie air code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thank you for your replies.

    If I get this right:

    With a cursor I would write
    Code:
    DECLARE myCursor CURSOR FOR
    SELECT A.id, A.column1, B.column2
    FROM myTableA AS A
         INNER JOIN myTableB AS B ON
            A.fk = B.id
    WHERE B.columnX = 'blablabla';
    OPEN myCursor;
    FETCH NEXT FROM myCursor INTO @id, @column1, column2;
    The cursor takes care of going through all the records one at a time.

    With a WHILE construct, I would have to redesign the query in such a way that it will give me exactly 1 record (or 0 at the end), making sure that it will never skip a record or returns one record multiple times.
    Code:
    SET @id = 0
    
    SELECT @id = id, @column1 = column1, column2 = column2
    FROM (SELECT MIN(id) as minId
          FROM myTableA AS A
             INNER JOIN myTableB AS B ON
                A.fk = B.id
          WHERE B.columnX = 'blablabla' and 
                A.id > @id
          ) as T
          INNER JOIN myTableA AS A ON 
             T.minId = A.id
          INNER JOIN myTableB AS B ON
             A.fk = B.id
    I'm not used to writing queries this way. Is the way I rewrote the query the best way to do it? Or is there a better best-practice to accomplish this?

    In my first time experience, I would say working with a WHILE construct instead of a cursor, adds complexity to the query, somehow obfuscating the logic of the query. The extra complexity will likely make the query more expensive to process by the DBMS.
    But one gains in return that while the cursor might block other people, the WHILE construct doesn't.

    Is the net effect an increase in performance?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There aren't really straight forward pros and cons. For example, not locking the table might be considered a bad thing in some situations.

    You are right though - I can't think of a time I've used loops except for admin tasks in a long time. As such I usually don't reference multiple tables. When I have I typically cache them and loop through the cached table.

    There isn't a best practice - it is a tool that is appropriate for some jobs and not for others. I'll repeat - the main reason I use loops is because I can't be bothered learning cursor syntax. I have no idea what the performance differences are since the stuff I'll be using it for won't really need to be performant. *

    * or at least optimising the loop won't make much difference.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2009
    Posts
    24
    Thanks so much to everyone who responded. I got so much hassle about cursors that I decided to abandon the cursor. I'm am so sorry I did not return to post my change of heart but busy is an understatment for the state I am in.
    Thanx again,
    Trudye

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •