Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Cursor in Stored Procedure Expand / Collapse
Author
Message
Posted Sunday, March 31, 2013 7:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 19, 2014 1:06 PM
Points: 69, Visits: 170
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!



Post #1437289
Posted Sunday, March 31, 2013 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1437290
Posted Sunday, March 31, 2013 10:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
I like what BitBucket suggested. But stay far away from cursors. Remove that junk code and use efficient code.

Andrew SQLDBA
Post #1437302
Posted Monday, April 1, 2013 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:19 AM
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
Post #1437437
Posted Monday, April 1, 2013 8:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
SQL Server does not Loop thru rows. SQL Server is Set Based.

Please do not indicate that a database loops thru rows.

Andrew SQLDBA
Post #1437468
Posted Monday, April 1, 2013 10:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 10:32 AM
Points: 546, Visits: 1,063
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.
Post #1437517
Posted Monday, April 1, 2013 10:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 20,863, Visits: 32,901
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;





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)
Post #1437526
Posted Monday, April 1, 2013 10:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:19 AM
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.
Post #1437528
Posted Monday, April 1, 2013 10:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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 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)
Post #1437530
Posted Monday, April 1, 2013 11:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 20,863, Visits: 32,901
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.



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)
Post #1437533
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse