Cursor in Stored Procedure

  • Hi,

    I am a SQL newbie and need to update code in a Stored Procedure that has a cursor. Is it possible to have multiple queries in a cursor?

    Currently, we are checking for duplicates in a database table when importing an Excel upload file. Now, we also want to check for potential duplicates in an Excel upload file and if the Excel file record falls into an already exisiting date range (passing start and end dates). I do not need help with the queries, but just wanted to give a little background information. I need help to understand how to get three queries to work in a cursor and make a decision if duplicates are found.

    Can someone please provide sample code on how to place three queries in a cursor and/or provide a good reference?

    Basically, on a high level this is what I want to do:

    Run Qry 1 - check for duplicates in database table

    Run Qry 2 - check for duplicates in file

    Run Qry 3 - check to see if record already exists in specific date range

    if no duplicates, then "0" count of dups

    if dup is found, than Count and error type description

    Thanks in advance, any help is much appreciated!

  • Without sample table definition(s), and sample data it is difficult to make a recommendation.

    That said, I would recommend that you look at the MERGE statement.

    For example:

    [ WITH <common_table_expression> [,...n] ]

    MERGE

    [ TOP ( expression ) [ PERCENT ] ]

    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]

    USING <table_source>

    ON <merge_search_condition>

    [ WHEN MATCHED [ AND <clause_search_condition> ]

    THEN <merge_matched> ] [ ...n ]

    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]

    THEN <merge_not_matched> ]

    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]

    THEN <merge_matched> ] [ ...n ]

    [ <output_clause> ]

    [ OPTION ( <query_hint> [ ,...n ] ) ]

    ;

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • I like what BitBucket suggested. But stay far away from cursors. Remove that junk code and use efficient code.

    Andrew SQLDBA

  • This code creates a table with duplicates. I copy the data into a temp table adding a row column. You could loop through the temp table by

    CREATE TABLE t1

    (

    Id INT,

    [DESC] varchar(50)

    )

    INSERT INTO t1 (id, [desc]) VALUES ( 1,'test 1')

    INSERT INTO t1 (id, [desc]) VALUES ( 1,'test 1')

    INSERT INTO t1 (id, [desc]) VALUES ( 2,'test 2')

    INSERT INTO t1 (id, [desc]) VALUES ( 3,'test 3')

    INSERT INTO t1 (id, [desc]) VALUES ( 3,'test 3')

    SELECT ROW_NUMBER() OVER(ORDER BY id) AS ROW,* INTO #t1 FROM t1 ORDER BY id

    SELECT * FROM #t1

    This will show you the id's that are duplicates.

    SELECT id FROM #t1 GROUP BY id HAVING COUNT(id) > 1

    2nd option would be to use the Row and loop through the rows and write the row number containing the duplicate to a table. Delete the rows from the temp table

    #t1 using the table created during the looping process. Then write the temp table back to your final table.

    DROP TABLE #t1

    DROP TABLE t1

  • SQL Server does not Loop thru rows. SQL Server is Set Based.

    Please do not indicate that a database loops thru rows.

    Andrew SQLDBA

  • AndrewSQLDBA (4/1/2013)


    SQL Server does not Loop thru rows. SQL Server is Set Based.

    Please do not indicate that a database loops thru rows.

    Andrew SQLDBA

    🙂

    Give me a set based statement that goes through all 300 of my tables and renames the table to [tablename]+[currentdate] (and I'll remind you that sp_ForEachTable is a systemic cursor) and I'll happily agree with both statements made about looping and cursors.

    Sometimes I think we tend tell others NEVER to use a cursor, gods forbid! Find any other way to do it! Evil things! Bad..bad...bad..

    When what we really mean is that cursors are sometimes overused or misused when a set-based statement would be more efficient. ie, try to find a set-based solution before falling back to a cursor or looping mechanism. Failing THAT, however, if a cursor is necessary, use a cursor. Just realize that there are performance issues involved in RBAR and be prepared to deal with them.

  • Erin Ramsay (4/1/2013)


    AndrewSQLDBA (4/1/2013)


    SQL Server does not Loop thru rows. SQL Server is Set Based.

    Please do not indicate that a database loops thru rows.

    Andrew SQLDBA

    🙂

    Give me a set based statement that goes through all 300 of my tables and renames the table to [tablename]+[currentdate] (and I'll remind you that sp_ForEachTable is a systemic cursor) and I'll happily agree with both statements made about looping and cursors.

    Sometimes I think we tend tell others NEVER to use a cursor, gods forbid! Find any other way to do it! Evil things! Bad..bad...bad..

    When what we really mean is that cursors are sometimes overused or misused when a set-based statement would be more efficient. ie, try to find a set-based solution before falling back to a cursor or looping mechanism. Failing THAT, however, if a cursor is necessary, use a cursor. Just realize that there are performance issues involved in RBAR and be prepared to deal with them.

    Okay, how about this:

    declare @SQLCmd nvarchar(max);

    select

    @SQLCmd = stuff((select char(13) + char(10) +

    'exec sp_rename @objname = N''' + schema_name(tab.schema_id) + '.' + tab.name + ''', @newname = N''' + schema_name(tab.schema_id) + '.' + tab.name + '_' + convert(varchar(10), getdate(), 112) + ''', @objtype = N''TABLE'''

    from sys.tables tab

    for xml path(''),TYPE).value('.','nvarchar(max)'),1,2,'');

    PRINT @SQLCmd;

    --exec sp_executesql @SQLCmd;

  • Back to helping the original user and to discuss your critque of the vernacular. . .

    declare @row int

    declare @count int

    declare @previd int

    declare @id int

    Create table #killRow

    (

    row int

    )

    select Row_Number() OVER(ORDER BY mytableID) as row, * INTO #mytemptable FROM MyTable...

    select count(*) from #mytemptable

    set @row = 1

    set @previd = 0

    while @row <= @count

    begin

    ... do stuff here with items from #mytemptable where row = @row

    select @id from #mytemptable where row = @row

    if (@id == @previd)

    begin

    insert into #killRow (row) values(@row)

    end

    select @previd = id from #mytemptable where row = @row

    set @row = @row + 1

    end

    delete from #mytemptable where row in ( select row from #killRow)

    select * into mycleanedtable from #mytemptable

    OR

    select * into mycleanedtable from #mytemptable where row not in ( select row from #killRow )

    My vernacular for looping is adequate to express the series of iterating over these rows. You can call it set based all you like, but the vernacular used to describe

    the process is more than adequate.

  • Erin Ramsay (4/1/2013)


    AndrewSQLDBA (4/1/2013)


    SQL Server does not Loop thru rows. SQL Server is Set Based.

    Please do not indicate that a database loops thru rows.

    Andrew SQLDBA

    🙂

    Give me a set based statement that goes through all 300 of my tables and renames the table to [tablename]+[currentdate] (and I'll remind you that sp_ForEachTable is a systemic cursor) and I'll happily agree with both statements made about looping and cursors.

    You said that like it would be challenging. Quite simple in fact.

    declare @MyQuery nvarchar(max) =

    (

    select 'exec sp_rename [' + name + '], [' + name + '_' + convert(varchar(20), getdate(), 112) + '];'

    from sys.tables

    for xml path('')

    )

    exec sp_executesql @MyQuery

    Sometimes I think we tend tell others NEVER to use a cursor, gods forbid! Find any other way to do it! Evil things! Bad..bad...bad..

    When what we really mean is that cursors are sometimes overused or misused when a set-based statement would be more efficient. ie, try to find a set-based solution before falling back to a cursor or looping mechanism. Failing THAT, however, if a cursor is necessary, use a cursor. Just realize that there are performance issues involved in RBAR and be prepared to deal with them.

    Yes they are horribly overused. Cursor are very important and serve as an invaluable tool when doing maintenance. They are not always evil but often they only require a change in the way we think about data. 😉

    --EDIT--

    It seems that while I was writing Lynn posted another version that is similar to mine.

    _______________________________________________________________

    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 (4/1/2013)


    Erin Ramsay (4/1/2013)


    AndrewSQLDBA (4/1/2013)


    SQL Server does not Loop thru rows. SQL Server is Set Based.

    Please do not indicate that a database loops thru rows.

    Andrew SQLDBA

    🙂

    Give me a set based statement that goes through all 300 of my tables and renames the table to [tablename]+[currentdate] (and I'll remind you that sp_ForEachTable is a systemic cursor) and I'll happily agree with both statements made about looping and cursors.

    You said that like it would be challenging. Quite simple in fact.

    declare @MyQuery nvarchar(max) =

    (

    select 'exec sp_rename [' + name + '], [' + name + '_' + convert(varchar(20), getdate(), 112) + '];'

    from sys.tables

    for xml path('')

    )

    exec sp_executesql @MyQuery

    Sometimes I think we tend tell others NEVER to use a cursor, gods forbid! Find any other way to do it! Evil things! Bad..bad...bad..

    When what we really mean is that cursors are sometimes overused or misused when a set-based statement would be more efficient. ie, try to find a set-based solution before falling back to a cursor or looping mechanism. Failing THAT, however, if a cursor is necessary, use a cursor. Just realize that there are performance issues involved in RBAR and be prepared to deal with them.

    Yes they are horribly overused. Cursor are very important and serve as an invaluable tool when doing maintenance. They are not always evil but often they only require a change in the way we think about data. 😉

    --EDIT--

    It seems that while I was writing Lynn posted another version that is similar to mine.

    Yes, and mine takes into account the possibility of multiple schemas. I noticed that after my first pass created a sp_rename for two tables with different schemas but the same name. Realized I needed to take that into account.

  • That's a nice bit of code there, Sean.

    While I've used dynamic sql to either a) build the list of commands I want to run, or b) run dynamic commands, I don't usually combine a) and b) to run a dynamic LIST of commands.. not a bad idea, maybe a little harder to debug than doing it with single command steps but definitely something to keep in mind.

  • I concede that this task can be done with a set-based command that generates a list of commands.. lol.. now let's consider a moment.

    Now consider your task may take a LARGE block of dynamic SQL (say about 6000 characters) that you're going to execute against all 300 tables.. do you still want to generate a character variable to hold this massive statement so that if (when) it breaks you'll get "Error on line 1 of...." or hey! put it in a procedure and THEN put it in your statement to save space.. but then you still get "Error on line 1 of..." when it breaks.

    Generally, I use a cursor because it's a non-production issue, it's developmental and I'm using it only to save myself typing time.. it's likely a one time event.. but I'd rather have ONE particular command fail that I can debug than have the whole block fail and try to figure out where it's falling down.

    The point I was trying to get across was that there ARE times an places that a cursor can be useful.. just need to be careful about when and where, you know?

  • Erin Ramsay (4/1/2013)


    I concede that this task can be done with a set-based command that generates a list of commands.. lol.. now let's consider a moment.

    Now consider your task may take a LARGE block of dynamic SQL (say about 6000 characters) that you're going to execute against all 300 tables.. do you still want to generate a character variable to hold this massive statement so that if (when) it breaks you'll get "Error on line 1 of...." or hey! put it in a procedure and THEN put it in your statement to save space.. but then you still get "Error on line 1 of..." when it breaks.

    Generally, I use a cursor because it's a non-production issue, it's developmental and I'm using it only to save myself typing time.. it's likely a one time event.. but I'd rather have ONE particular command fail that I can debug than have the whole block fail and try to figure out where it's falling down.

    The point I was trying to get across was that there ARE times an places that a cursor can be useful.. just need to be careful about when and where, you know?

    We aren't denying that there may be times when cursors are appropriate. I admit to using them for maintenance or one-time code. And yes, I have created dynamic code that actually exceeds 8000 bytes and does multiple commands.

  • Lynn Pettis (4/1/2013)


    And yes, I have created dynamic code that actually exceeds 8000 bytes and does multiple commands.

    Mind if I ask what made you break them into multiple commands?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I use a slightly different method to avoid some of the entitization problems FOR XML brings with it. Technically it is an undocumented method that I know of but I have not had trouble with it since SQL 2000 and I know of some other techniques that leverage it without trouble too:

    DECLARE @SQLCmd NVARCHAR(MAX) = N'';

    -- instead of using FOR XML just append the conjured string from each row to the same variable

    SELECT @SQLCmd += 'EXEC sys.sp_rename ' + --

    '@objname = N''' + SCHEMA_NAME(tab.schema_id) + '.' + tab.name + ''', ' + --

    '@newname = N''' + tab.name + '_' + CONVERT(VARCHAR(10), GETDATE(), 112) + ''', ' + --

    '@objtype = N''OBJECT'';' + -- OBJECT

    NCHAR(13) + NCHAR(10)

    FROM sys.tables tab;

    -- show the entire variable contents as an XML document...no truncation like with PRINT

    SELECT @SQLCmd AS [processing-instruction(query)]

    FOR XML PATH(''),

    TYPE;

    -- when ready, uncomment

    --EXEC sys.sp_executesql @SQLCmd;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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