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 ««12345»»»

Cursors for T-SQL Beginners Expand / Collapse
Author
Message
Posted Thursday, January 1, 2009 10:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 3, 2009 1:40 AM
Points: 3, 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
Post #628605
Posted Friday, January 2, 2009 12:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 7:06 AM
Points: 143, Visits: 548
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
Post #628616
Posted Friday, January 2, 2009 3:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 5, 2012 3:23 AM
Points: 79, Visits: 232
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)
Post #628666
Posted Friday, January 2, 2009 3:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 4:00 AM
Points: 29, Visits: 226
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.
Post #628683
Posted Friday, January 2, 2009 5:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 3:27 AM
Points: 313, Visits: 382
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.....
Post #628709
Posted Friday, January 2, 2009 5:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 2, 2009 1:39 PM
Points: 1, 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




Post #628734
Posted Friday, January 2, 2009 6:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 15,646, Visits: 28,027
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #628745
Posted Friday, January 2, 2009 6:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 4:00 AM
Points: 29, Visits: 226
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."
Post #628756
Posted Friday, January 2, 2009 6:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 15,646, Visits: 28,027
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 ;) 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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #628764
Posted Friday, January 2, 2009 11:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #628987
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse