SQL Server Performance

Instead of Cursors...

Discussion in 'Performance Tuning for DBAs' started by babuganesh2000, Mar 17, 2006.

  1. babuganesh2000 New Member

    DB Guru's,

    Please suggest me, the pros and cons of these two approaches.
    List out the situation/context to go for either of these approaches.
    Is there any other best way to do....

    Approach 1:
    SET NOCOUNT ON
    DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
    @message varchar(80)
    PRINT '-------- Utah Authors report --------'
    DECLARE authors_cursor CURSOR FOR
    SELECT au_id, au_fname, au_lname
    FROM authors
    WHERE state = 'UT'
    ORDER BY au_id
    OPEN authors_cursor
    FETCH NEXT FROM authors_cursor
    INTO @au_id, @au_fname, @au_lname
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT ' '
    SELECT @message = '----- Books by Author: ' +
    @au_fname + ' ' + @au_lname
    PRINT @message
    FETCH NEXT FROM authors_cursor
    INTO @au_id, @au_fname, @au_lname
    END
    CLOSE authors_cursor
    DEALLOCATE authors_cursor
    GO

    Approach 2:
    SET NOCOUNT ON
    DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
    @message varchar(80),@Error integer, @Rowcount integer
    PRINT '-------- Utah Authors report --------'
    SELECT @au_id = ''
    WHILE 1 = 1
    BEGIN
    SELECT @au_id = MIN(au_id)
    FROM authors WHERE state = 'UT'
    AND (au_id > @au_id)
    SELECT @Error = @@error,
    @Rowcount = @@rowcount
    IF (@Rowcount = 0) OR (@au_id IS NULL)
    BREAK
    SELECT @au_id = au_id,
    @au_fname = au_fname,
    @au_lname = au_lname
    FROM authors
    WHERE state = 'UT'
    AND au_id = @au_id
    ORDER BY au_id
    PRINT ' '
    SELECT @message = '----- Books by Author: ' +
    @au_fname + ' ' + @au_lname
    PRINT @message
    END --WHILE Loop through each record

    Thanks,
    Ganesh
  2. Adriaan New Member

    A proper stored procedure doesn't include PRINT statements: you should use PRINT only for troubleshooting/debugging.

    You must remove all instances of PRINT from production code, because PRINT often confuses client applications, leading to unexpected errors.

    Just do straight SELECTs to list the data. Let the client application worry about headers and footers.
  3. satya Moderator

    Absolutely a valid point, this is useful when an error occurs, the PRINT statement produces the "Error Occurred" message and this could create another problem with ADO. ADO considers these messages to be errors of severity 0 - too low to raise an exception - but nevertheless the messages are written to ADO's errors collection.

    refer tohttp://www.novicksoftware.com/TipsAndTricks/Tips-erorr-handling-in-a-stored-procedure.htm about error hanlding in SPs.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page