Query Help- Cursor change into FOR WHILE LOOP, TEMP TABLE

  • I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed me that the use of CURSORs is not accepted here. I need help converting my CURSORs into FOR WHILE LOOP format. Someone please help me out. Here is the CURSOR:

    CREATE PROCEDURE usp_PreStageValidation

    @SQL_str1 NVARCHAR(max),

    @SQL_str2 NVARCHAR(max),

    @SQL_str3 NVARCHAR(max),

    @SQL_str4 NVARCHAR(max),

    @SQL_str5 NVARCHAR(max)

    AS

    BEGIN TRY

    DECLARE

    @Catalog nvarchar(128),

    @Table nvarchar(128),

    @Column nvarchar(128),

    @Message nvarchar(128);

    SET NOCOUNT ON

    Declare @cat

    Select @cat

    EXEC sp_executeSQL @SQL_str1, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

    OPEN syd_cursor1;

    FETCH NEXT FROM syd_cursor1 INTO @cat,@tbl;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    @msg = 'This Stage table is not existed in this SOURCE database';

    INSERT INTO syd_LogTable VALUES(@cat,@tbl,NULL,@msg);

    FETCH NEXT FROM db_cursor1 INTO @cat,@tbl;

    END;

    CLOSE syd_cursor1;

    DEALLOCATE syd_cursor1;

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_CATALOG = @cat AND --When @cat contains Source Database name

    TABLE_SCHEMA = 'dbo' AND

    TABLE_NAME = 'tbPlanStartEnd' ) --and this table is not existed in it

    BEGIN

    INSERT INTO syd_LogTable

    VALUES(@cat, 'tbPlanStartEnd', NULL, 'This Table is Created Newly');

    END;

    --------------

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_CATALOG = @cat AND --When @cat contains Source Database name

    TABLE_SCHEMA = 'dbo' AND

    TABLE_NAME = 'tbPlanGroup' ) --and this table is not existed in it

    BEGIN

    INSERT INTO syd_LogTable

    VALUES(@cat, 'tbPlanGroup', NULL, 'This Table is Created Newly');

    End

    ELSE if

    NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_CATALOG = @cat AND --When @cat contains Source Database name

    TABLE_SCHEMA = 'dbo' AND

    TABLE_NAME = 'tbPlanGroup' AND

    COLUMN_NAME = 'grp_name' ) --and this column is not existed in it

    BEGIN

    INSERT INTO syd_LogTable

    VALUES(@cat, 'tbPlanGroup', NULL, 'This Table is added with column grp_name');

    END;

    END;

    DECLARE syd_cursor2 CURSOR FOR

    EXEC sp_executeSQL @@SQL_str2, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

    OPEN syd_cursor2;

    FETCH NEXT FROM syd_cursor2 INTO @cat,@tbl;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    @msg = 'This Stage table is not existed in this STAGE database';

    INSERT INTO syd_LogTable VALUES(@cat,@tbl,NULL,@msg);

    FETCH NEXT FROM db_cursor2 INTO @cat,@tbl;

    END

    CLOSE syd_cursor2;

    DEALLOCATE syd_cursor2;

    DECLARE syd_cursor3 CURSOR FOR

    EXEC sp_executeSQL @@SQL_str3, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

    OPEN syd_cursor3;

    FETCH NEXT FROM syd_cursor3 INTO @cat,@tbl,@col;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    @msg = 'This Stage Column is not existed in this Table of this SOURCE database';

    INSERT INTO syd_LogTable VALUES(@cat,@tbl,@col,@msg);

    FETCH NEXT FROM db_cursor3 INTO @cat,@tbl,@col;

    END

    CLOSE syd_cursor3;

    DEALLOCATE syd_cursor3;

    DECLARE syd_cursor4 CURSOR FOR

    EXEC sp_executeSQL @@SQL_str4, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

    OPEN syd_cursor4;

    FETCH NEXT FROM syd_cursor4 INTO @cat,@tbl,@col;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    @msg = 'This Stage Column is not existed in this Table of this STAGE database';

    INSERT INTO syd_LogTable VALUES(@cat,@tbl,@col,@msg);

    FETCH NEXT FROM db_cursor4 INTO @cat,@tbl,@col;

    END;

    CLOSE syd_cursor4;

    DEALLOCATE syd_cursor4;

    DECLARE syd_cursor5 CURSOR FOR

    EXEC sp_executeSQL @@SQL_str5, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

    OPEN syd_cursor5;

    FETCH NEXT FROM syd_cursor5 INTO @cat,@tbl,@col;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    @msg = 'The Data Type of this Stage Column of this Table is NOT same as in this SOURCE database';

    INSERT INTO syd_LogTable VALUES(@cat,@tbl,@col,@msg);

    FETCH NEXT FROM db_cursor5 INTO @cat,@tbl,@col;

    END

    CLOSE syd_cursor5;

    DEALLOCATE syd_cursor5;

  • morepainot (7/25/2012)


    I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed me that the use of CURSORs is not accepted here. I need help converting my CURSORs into FOR WHILE LOOP format. Someone please help me out. Here is the CURSOR:

    I don't have the time today to help you rewrite these but I do have time to tell you that if you're going to convert the cursors to the ol "Temp Table and While Loop", you're absolutely wasting your time. Behind the scenes, a a nice STATIC, FORWARD ONLY, READ ONLY cursor IS a Temp Table/While Loop combination behind the scenes. The conversion will do NOTHING to save on resources or duration.

    To wit, this needs to be converted to SET BASED code if at all possible and I'm pretty sure that it's possible.

    Also, if you really want to be respected in the world of SQL, particularly at your job, get into the habit of documenting the "WHY" of your code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi

    I am sending sample procedure to avoid cursors. Implement this logic in your procedures. This may help you.

    declare @test_tab TABLE

    (

    [id1] [int] NULL,

    query [varchar](50) NULL,

    [result] [bit] NULL)

    insert into @test_tab values(1,'select 30 - 50',Null)

    insert into @test_tab values(2,'select 70 - 50',Null)

    insert into @test_tab values(3,'select 20 - 20',Null)

    select * from @test_tab

    declare @start int=1,@vquery varchar(max),@result int

    while @start <= (select COUNT(*) from @test_tab)

    begin

    select @vquery = query from @test_tab where id1 = @start

    exec (@vquery)

    set @start = @start + 1

    end

    Regard

    Siva Kumar J:-)

  • Jeff and Sivaj, thank you guys for your help.

    @jeff; I understand what you said and I will keep that in mind for the next post. The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP. The company I am contracted for advised me not to use them. I am a simple developer with not any serious developing experience. Doing this was a challenge for me but I believe I converted it correctly. Thank you.

    Sivaj; Thanks for that. I appreciate it.

  • morepainot (7/26/2012)


    ...The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP. ...

    I would love to see the tests on that statement. my question is how do you know.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP.

    Who said CURSOR is slower than WHILE loops? Didn't you asked them reason for the same?

    While working with cursors, there are lot of options you can set to make them more performant, check the comments above by @jeff.

    Also check this blog post where I've shown CURSORS giving good performance than WHILE LOOPS: http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/[/url]

  • morepainot (7/26/2012)


    The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP.

    Like I said, that's patently and absolutely not true. The WHILE LOOP is usually what makes cursor usage so slow and I've proven that many times for companies that think other wise.

    The key to performance here is to get rid of the WHILE loop. Period.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • morepainot (7/26/2012)


    Jeff and Sivaj, thank you guys for your help.

    @jeff; I understand what you said and I will keep that in mind for the next post. The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP. The company I am contracted for advised me not to use them. I am a simple developer with not any serious developing experience. Doing this was a challenge for me but I believe I converted it correctly. Thank you.

    Sivaj; Thanks for that. I appreciate it.

    Agreed. This is nothing more than 5 insert statements. No need to loop through each row for this at all. If you really want to impress your contractor turn this into a set based solution.

    The other thing about this procedure is that appears to be wide open for sql injection. You are receiving parameters and executing them. :w00t:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • manub22 (7/26/2012)


    The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP.

    Who said CURSOR is slower than WHILE loops? Didn't you asked them reason for the same?

    While working with cursors, there are lot of options you can set to make them more performant, check the comments above by @jeff.

    Also check this blog post where I've shown CURSORS giving good performance than WHILE LOOPS: http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/[/url]

    Interesting blog post Manoj. It is true you can make cursors more optimal than a while loop but why bother with them at all? In your examples there is absolutely nothing that can't be done far quicker by getting rid of the loop entirely.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • It is true you can make cursors more optimal than a while loop but why bother with them at all? In your examples there is absolutely nothing that can't be done far quicker by getting rid of the loop entirely.

    Exactly @sean, in my example there is nothing one should use a CURSOR.

    This was only to show people who believe that CURSORS are evil and use WHILE loops happily. I'm not favoring CURSORS, but just want to bust that myth.

    Lot of problems can be easily done by SET based approach rather than using CURSORS. People not expert in writing SQL queries and/or not aware of new features end up using CURSORS.

  • manub22 (7/26/2012)


    It is true you can make cursors more optimal than a while loop but why bother with them at all? In your examples there is absolutely nothing that can't be done far quicker by getting rid of the loop entirely.

    Exactly @sean, in my example there is nothing one should use a CURSOR.

    This was only to show people who believe that CURSORS are evil and use WHILE loops happily. I'm not favoring CURSORS, but just want to bust that myth.

    Lot of problems can be easily done by SET based approach rather than using CURSORS. People not expert in writing SQL queries and/or not aware of new features end up using CURSORS.

    I gotcha. I just tend to side with Jeff on this topic. Why use either? They do have their place but those are very specific types of administrative tasks. In general, just don't loop or cursor. 😛

    @jeff - You need start the official anti-RBAR alliance and offer membership to those who have proven to be members. We could have a secret handshake and membership cards that offer nothing. 🙂 It is like the secret society that only members can join.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I have no problem using CURSOR, as you can see by my original script. The people who have contracted have the problem with CURSOR. I dont care about using either or its just to make them happy. Im not going to go against their wishes and do what makes me happy. At the end of the day THEY sign my checks. If theyre happy then Im happy. It might take some extra time but if the work is done then its all good. Thanks for everyones input. I may have some coming in the following week or two.

  • morepainot (7/26/2012)


    I have no problem using CURSOR, as you can see by my original script. The people who have contracted have the problem with CURSOR. I dont care about using either or its just to make them happy. Im not going to go against their wishes and do what makes me happy. At the end of the day THEY sign my checks. If theyre happy then Im happy. It might take some extra time but if the work is done then its all good. Thanks for everyones input. I may have some coming in the following week or two.

    I think you missed my point. I think YOU SHOULD have an issue with using a cursor. Using a cursor or a while loop for a series of inserts is like taking a Yugo to a drag race. It will finish but it takes WAY longer than it needs to.

    I don't want to get into a long drawn out discussion but I am never satisfied with "good enough".

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (7/26/2012)


    @Jeff - You need start the official anti-RBAR alliance and offer membership to those who have proven to be members. We could have a secret handshake and membership cards that offer nothing.

    How about membership cards that actually DO mean something... especially to future employers. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (7/26/2012)


    Sean Lange (7/26/2012)


    @Jeff - You need start the official anti-RBAR alliance and offer membership to those who have proven to be members. We could have a secret handshake and membership cards that offer nothing.

    How about membership cards that actually DO mean something... especially to future employers. 😉

    I do often ask about splits in the interviews and any candidate that mentions Mr Moden's splitter ends on the top of the list.

Viewing 15 posts - 1 through 15 (of 137 total)

You must be logged in to reply to this topic. Login to reply