Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor in Stored Procedure


Cursor in Stored Procedure

Author
Message
Meatloaf
Meatloaf
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 421
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!
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 25280
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

Before posting a performance problem please read
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
I like what BitBucket suggested. But stay far away from cursors. Remove that junk code and use efficient code.

Andrew SQLDBA
retm1109
retm1109
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 31
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
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
SQL Server does not Loop thru rows. SQL Server is Set Based.

Please do not indicate that a database loops thru rows.

Andrew SQLDBA
Erin Ramsay
Erin Ramsay
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 1099
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


Smile

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.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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


Smile

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;




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
retm1109
retm1109
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 31
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16592 Visits: 17024
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


Smile

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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


Smile

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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search