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


Cursors for T-SQL Beginners


Cursors for T-SQL Beginners

Author
Message
slippers
slippers
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 5
Hi

One question: if considering whether to use Cursors or alternatives, how does trigger firing influence the decision?

I mean, when using a cursor to perform an insert statement with each loop, triggers on the target table will fire for each row being inserted.

When using alternatives, is there any way to ensure that triggers will process each insert?

Thanks
Divya Agrawal
Divya Agrawal
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 604
Yes, triggers will always be fired if you have return it on an insert statement. It is independent on whether you are using cursors or using any other alternative to insert rows in a table..

Try using "no count off" and writing an insert or update statement in trigger. It will show you the rows affected

--Divya
sudhanva
sudhanva
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 236
Extending the table structure given in the article[url=http://www.sqlservercentral.com/articles/cursors/65136/][/url]

I have added an extra table for UserListing.

CREATE TABLE dbo.tblUser ( codUser INT, codName VARCHAR(20),PRIMARY KEY (codUser));

insert into dbo.tblUser values (1, 'abc');
insert into dbo.tblUser values (2, 'def');



When i execute the following query
SELECT TU.codName ,-- @AllPhones = @AllPhones + 
CASE WHEN P.ListThisNumber = 1
THEN P.PhoneNumber ELSE '***********'END
+ ' , '
FROM dbo.tblPhone P
INNER JOIN dbo.tblUser TU ON P.codUser = TU.codUser
INNER JOIN dbo.tblPhoneType PT ON P.codPhoneType = PT.codPhoneType



I will get result like this
---------------------------------
codName (no column name)
abc 1 281 444 5555 ,
abc 55 11 4582 2752 ,
abc *********** ,
def 1 XXX XXX XXXXX ,
-----------------------------------

But i want a result like this
---------------------------------
codName (no column name)
abc 1 281 444 5555 , 55 11 4582 2752 , *********** ,
def 1 XXX XXX XXXXX ,
-----------------------------------

I know we can achieve the above result using cursors or CTE.

But i am expecting the above result with a query something like below:
DECLARE @AllPhones VARCHAR(1000)

SET @AllPhones = ''
SELECT TU.codName , @AllPhones = @AllPhones +
CASE WHEN P.ListThisNumber = 1
THEN P.PhoneNumber ELSE '***********'END
+ ' , '
FROM dbo.tblPhone P
INNER JOIN dbo.tblUser TU ON P.codUser = TU.codUser
INNER JOIN dbo.tblPhoneType PT ON P.codPhoneType = PT.codPhoneType

GO


But we will get an error
[color=#ff0000]A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.[/color]

My question is, Is there any work around for this? (Without using cursors or CTE)
wagner crivelini
wagner crivelini
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 282
In fact, concatenating strings will not do on this example.
This works when you have one scalar value to return, instead of a whole table.
But there are workarounds, of course.
I don't intend here to suggest you a "best-practice" approach, but simply something that can help you.
For instance, try declaring a table variable (let's call it @tmpResult) with fields codName and PhoneNumbers.
Then create a loop (using WHILE stement) over each record on dbo.tblUser.
Within the loop, you run this SELECT statement you used to concatenate PhoneNumbers for each codUser.
Finally you should populate (using INSERT statement) @tmpResult with each record (@vchName, @AllPhones)
At the end, you show the records within the table variable (with a SELECT statement).
I hope it can help.
Linson.Daniel
Linson.Daniel
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 391
Well just to put in query terms what wagner have just suggested

DECLARE @AllPhones VARCHAR(1000),@Count INT,@maxcount int,@mincount int,@codname varchar(100),@rowcounted int,
@Row int,@codename varchar(100)

SET @AllPhones = ''

create table #Results(codname varchar(1000), PhoneNo varchar(1000))
create table #TempTbl(rowid int identity(1,1),
codname varchar(1000), rowcounted int)
create table #TempTbl2(rowid int identity(1,1),
codname varchar(1000), phonenumber varchar(100))
insert into #TempTbl
SELECT TU.codName , count(*)
FROM dbo.tblPhone P
INNER JOIN dbo.tblUser TU ON P.codUser = TU.codUser
INNER JOIN dbo.tblPhoneType PT ON P.codPhoneType = PT.codPhoneType
group by TU.codName

insert into #TempTbl2
SELECT TU.codName ,
CASE WHEN P.ListThisNumber = 1
THEN P.PhoneNumber ELSE '***********'END
FROM dbo.tblPhone P
INNER JOIN dbo.tblUser TU ON P.codUser = TU.codUser
INNER JOIN dbo.tblPhoneType PT ON P.codPhoneType = PT.codPhoneType

select @mincount = min(rowid) from #TempTbl
select @maxcount = max(rowid) from #TEmpTbl
select @Count = @mincount

While @Count < = @maxcount
begin

select @codename = codname , @rowcounted = rowcounted
from #TempTbl
where rowid = @Count

select @AllPhones = @AllPhones + ISNULL(phonenumber,'') + ','
from #TempTbl2
where codname = @codename

insert into #Results values(@codename , @AllPhones)
set @Count = @Count + 1
SET @codename = ''
SET @AllPhones = ''
end

select * from #Results
drop table #TempTbl
drop table #TempTbl2
drop table #Results


Well this is ofcourse not a pretty query ...nevertheless worth a look and definitely an alternate solution to cursors.....
robert.letts
robert.letts
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
Excellent article on cursors, Wagner.

Here is a weird problem that made me choose a cursor. The incoming data was in a header / detail format. The header had 5 columns: ICN Code, Officer, Department, Function, and Message. Essentially, it is a Money Transfer by Wire application running on a Tandem system. The data provisioning was in pure TAB Delimited Text, so I used OPENROWSET and a format file to load the daily files of 50,000 rows.

But the rows were really weird. The header had data in 4 out of five columns, and the detail had data in one out of five columns. The rows between headers were the details for the prior header. Where the last column (Message) was NULL on the header marked the end of the last header - the Message being NULL was a sign that the it was a header row. Where the Message was not NULL there were all kinds of "field codes", where each field code has very odd and specific flags and options. Each row of the message had space for 2000 VARCHARS. Row counts varied between headers too - not all the headers had the same number of Message rows.

The problem was to pick up the Currency, Amount, Customer ID, and Originating institution from each of the Message Rows for each of the headers. I used a cursor, much to my dismay. It is slow, but it works and gives me fine-grained control.

I would like to try a set based approach too. If you pick up this comment, let me know, and I can send you more details.

Bob
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17609 Visits: 32267
I like the article. Showing common cursor practices and a nice set-based approach to replace them is absolutely the right way to go. Unfortunately, as Jeff already pointed out, you then try to slip out of the concept that cursors are, to say the least, problematic by this, "But when replacing cursors, we are letting go some important features that only cursors can provide to your code. You should not take the avoid-cursors advice as rule, but as a hint. Each situation deserves attention, as you can have a fast and well-designed code using cursors. And, in the other hand, you can also find poor-performance codes specially created to avoid cursors."

Adding it to, what was otherwise a really well done article, brings up issues. Beginners shouldn't be advised that avoiding TSQL cursors is a "hint." Far too much horrifically bad code has been written by beginners because they're not thinking in sets. By suggesting that they don't need to, you, as the expert who wrote the article, are saying that it's OK to write the cursor. There are situations where cursors work better. Itzik Ben-Gan shows several during his advanced TSQL course. But they're rather arcane & odd situations, not the normal, day-to-day, street level code that most people have to deal with. There are also situations where, because of other aspects of the code, cursors don't matter, such as running backups or consistency checks against a list of databases on the server. A cursor works fine there.

Mr. Roughgarden's comments can show the validly complex situation where a cursor makes sense, but, again, I suspect that's the exception, not the rule, in his code as well.

Making these types of broad, sweeping statements, especially after you've established, quite well, exactly why cursors are so problematic, pretty much demands that you back them up. So I assume you've got another article in the works?

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
wagner crivelini
wagner crivelini
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 282
Well, I understand the fuzz, although I didn't mean to create it.
When I wrote this article I had in mind that not all T-SQL beginner are completely unaware of database design.
I have seen lots of bad code written by developers who simply were more familiar to other RDBMS's and took for granted that the truth for other RDBMS's is valid for SQL SERVER too.
Besides, I didn't mean novice database developers should not be scared about cursors. I tried to compare the performance of the (simple) alternatives I showed just to let them feel where they would be stepping on when using cursors.
But I also believe cursors are not so bad they shouldn't exist.
I've used cursors myself several times.
My point in this article is "Whenever you suspect your code is not running as expected, no matter it uses cursors or not, you should check your code's execution plan. This is the ultimate information you have to assess your code's performance."
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17609 Visits: 32267
wagner crivelini (1/2/2009)
Well, I understand the fuzz, although I didn't mean to create it.
When I wrote this article I had in mind that not all T-SQL beginner are completely unaware of database design.
I have seen lots of bad code written by developers who simply were more familiar to other RDBMS's and took for granted that the truth for other RDBMS's is valid for SQL SERVER too.
Besides, I didn't mean novice database developers should not be scared about cursors. I tried to compare the performance of the (simple) alternatives I showed just to let them feel where they would be stepping on when using cursors.
But I also believe cursors are not so bad they shouldn't exist.
I've used cursors myself several times.
My point in this article is "Whenever you suspect your code is not running as expected, no matter it uses cursors or not, you should check your code's execution plan. This is the ultimate information you have to assess your code's performance."


Let me reiterate before I whup on you some more Wink I think this is is a very well done article.

Unfortunately, another point you're bringing up now:
"Whenever you suspect your code is not running as expected, no matter it uses cursors or not, you should check your code's execution plan. This is the ultimate information you have to assess your code's performance."


Actually, no, it's not the ultimate. Unfortunately, the execution plan can present you with bad information. It can show you that an operation is costly when it is not, or cheap when the operation is expensive. I'm not saying you shouldn't use them. Hell, I wrote a book on them. I'm just saying that they're only one piece of the puzzle. You have to combine them with other information like I/O and execution times. Then, through repitition on the tests, you have the information you need.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9452 Visits: 9517
But when replacing cursors, we are letting go some important features that only cursors can provide to your code.


Assuming that we are talking about SQL Server 2005/2008, I know of no feature that "only cursors can provide", other than intentionally slowing down your code (which believe it or not, can be desirable). I am aware of some capabilities that only procedure-based loops (of which cursors are the least desirable) can provide, but they are vanishingly obscure and obtuse in real application.

All of the usual excuses for using cursors are based on misunderstandings of T-SQL capabilities and how SQL code should be designed and managed.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
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