December 31, 2008 at 9:37 pm
Comments posted to this topic are about the item Cursors for T-SQL Beginners
January 1, 2009 at 3:36 am
Good one for beginners. Excellent explanation...
January 1, 2009 at 11:07 am
I wonder if poor Mr. Wagner Crivelini is aware of the recent firestorm of posts engendered by an article writer who had the audacity to take on cursors on this website! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 1, 2009 at 11:37 am
article is super-duper for beginners... thank you
January 1, 2009 at 11:46 am
Make no doubt about it... Cursors are an advanced subject and no new user of T-SQL should be allowed anywhere near a cursor or any other form of RBAR until they have had at least of year of intense set based training and experience. There are no redeeming qualities of cursor usage, especially in SQL Server 2005 and beyond, except that people who don't really know anything about a database can get in and try to do stuff at great expense to the server.
But when replacing cursors, we are letting go some important features that only cursors can provide to your code.
Like what? The ability for a row to fail insert because you didn't validate the information before you tried to insert it but don't want the other rows to fail? That's spaghetti code (throw it against the wall and see if it sticks) and it's both foolish and irresponsible... rollbacks are incredibly expensive compared to even using a cursor.
I believe that articles like this one serve as a great disservice to anyone new to databases and believe that it's horribly irresponsible of an author to try to bring any credibility to any form of RBAR programming in any RDBMS.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 1, 2009 at 12:44 pm
You don't have to use a cursor to concatenate a string.
DECLARE @myVar varchar(MAX)
SET @myVar = ''
SELECT @myVar = @myVar + mycolumn + ' '
FROM myTable
SELECT @myVar
January 1, 2009 at 1:18 pm
gryphonsclaw (1/1/2009)
You don't have to use a cursor to concatenate a string.DECLARE @myVar varchar(MAX)
SET @myVar = ''
SELECT @myVar = @myVar + mycolumn + ' '
FROM myTable
SELECT @myVar
The author also pointed that out in the article.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 1, 2009 at 7:32 pm
OK, I'll take up the case for using cursors for some types of processing. I've posted below a copy of a stored procedure I just finished. It uses a read-only cursor to loop through a set of records indicating when each lot starts being processed in each of a facility's processing lines. For each row as appropriate, the code ultimately calls two other stored procedures that write 'Attribution' and 'Association' events to another database of queued events. Anyway, long story short, there is no way you could do this processing in a set-based manner. Period.
Now, it could be argued that I should have pulled all this logic into a C# routine, and that may be the case, but I don't see much advantage, except that Visual Studio is a vastly better IDE. If I did do it in C#, then I'd still be issuing multiple calls to the event writing procedures, but now from within a C# loop. A loop is a loop and this keeps everything internal to SQL, without going back and forth between a windows service and SQL.
BTW, I also think that using cursors for one-off task is harmless. So what if it takes a second as opposed to a tenth of a second, when I save 5 minutes in development time by using a simple cursor?
OK. Let the sticks and stones begin. 😉
Jeff Roughgarden, Ph.D., MCSD, MCDBA
ALTER procedure [dbo].[uspDCLateBoundLotInfo](
@intFileDefinitionID int = 2
, @strAttributeSetFamilyName varchar(100)='Late Bound Product Lot Attribution'
)as
set nocount on
CREATE TABLE #tblTripleValue
( strName varchar(255)
, strValue varchar(255)
, strTimeStamp varchar(255)
);
DECLARE curX CURSOR READ_ONLY FOR
select strValue00 as strPK, strValue01 as strFacility, strValue02 as strLine
, dbo.ufnDateTimeFromNETDateString(strValue03) as datLotStart, strValue04 as strLotID
from dbo.tblFileData
where intFileDefinitionID = 2 and strValue05<>'x'
order by strFacility, strLine, datLotStart
declare @intChildCountint
declare @datFromdatetime
declare @datTodatetime
declare @strFacilityCurrent varchar(255)
declare @strLineCurrentvarchar(255)
declare @strLotIDCurrentvarchar(255)
declare @strPKCurrentvarchar(255)
declare @strFacilityvarchar(255)
declare @strLinevarchar(255)
declare @strLotIDvarchar(255)
declare @strPKvarchar(255)
declare @guidNewuniqueidentifier
declare @strNameValueListvarchar(255)
declare @strTripleValueListvarchar(8000)
open curX
--get earliest row
fetch next from curX into @strPKCurrent, @strFacilityCurrent, @strLineCurrent, @datFrom, @strLotIDCurrent
if @@fetch_status<>0 return
while(1=1)
begin--loop
--get next row
fetch next from curX into @strPK, @strFacility, @strLine, @datTo, @strLotID
if @@fetch_status<>0 break
--check to see if there are any child containers for test below
select @intChildCount=count(*)
from dbo.tblReadStreamCases
where datCaseRead >= @datFrom-- in time range
and datCaseRead < @datTo
and intStationID in (-- from a station of the current line
SELECT S.intStationID
FROM dbo.tblLines AS L INNER JOIN
dbo.tblStations AS S ON L.intLineID = S.intLineID INNER JOIN
dbo.tblFacilities F ON L.intFacilityID = F.intFacilityID
WHERE (L.strLineName = @strLineCurrent)
AND (F.strFacility = @strFacilityCurrent)
) and strCaseReading not in(--not a child of any other container
select strItemReading--think this is applied at end;
--if not, could be peformance issue. <<<
from tblReadStreamCases
)
if @strFacility = @strFacilityCurrent and @strLine = @strLineCurrent and @intChildCount>0
begin--if block
--write attribution event (This is hard-wired for .)
--Parameters are those defined for ' Late Bound Product Lot Attribution'
set @strNameValueList='Pack Date|' + cast(@datFrom as varchar) + '|Lot|'
+ @strLotIDCurrent
set @guidNew=NewID()
--print 'NameValueList= ' + @strNameValueList
exec dbo.uspQueueInsertAttributionEvent 'FacilityTag', @guidNew
, @strAttributeSetFamilyName, @strNameValueList
--create a table to hold triples of parent and child containers
--for the association event
truncate table #tblTripleValue
insert into #tblTripleValue
select 'FacilityTag' as strType, cast(@guidNew as varchar(50))as strValue
, cast(getdate() as varchar(50)) as strTimeStamp
UNION
select distinct
(case when left(strCaseReading,2)='FT' then 'FacilityTag'
else 'CodeRead' end) as strType
, strCaseReading as strValue
, cast(datCaseRead as varchar(50)) as strTimeStamp
from dbo.tblReadStreamCases
where datCaseRead >= @datFrom-- in time range
and datCaseRead < @datTo
and intStationID in (-- from a station of the current line
SELECT S.intStationID
FROM dbo.tblLines AS L INNER JOIN
dbo.tblStations AS S ON L.intLineID = S.intLineID INNER JOIN
dbo.tblFacilities F ON L.intFacilityID = F.intFacilityID
WHERE (L.strLineName = @strLineCurrent)
AND (F.strFacility = @strFacilityCurrent)
) and strCaseReading not in(--not a child of any other container
select strItemReading--think this is applied at end;
--if not, could be peformance issue. <<<
from tblReadStreamCases
)
--convert triple value table to triple value string
set @strTripleValueList=''
select @strTripleValueList=@strTripleValueList
+ strName + '|' + strValue + '|' + strTimeStamp + '|'
from #tblTripleValue
set @strTripleValueList=left(@strTripleValueList, len(@strTripleValueList)-1)
--write the association event
--print 'TripleValueList= ' + @strTripleValueList
exec dbo.uspQueueInsertAssociationEvent @strTripleValueList
end--if block
--mark row as processed, except for last;
--Parameters are those defined for Late Bound Product Lots' file definition
if @strFacility = @strFacilityCurrent and @strLine = @strLineCurrent
update dbo.tblFileData
set strValue05 = 'x'
where strValue00=@strPKCurrent
and intFileDefinitionID=@intFileDefinitionID
--update current items
set @strPKCurrent = @strPK
set @strFacilityCurrent = @strFacility
set @strLineCurrent = @strLine
set @datFrom = @datTo
set @strLotIDCurrent = @strLotID
end--loop
close curX
deallocate curX
drop table #tblTripleValue
January 1, 2009 at 8:39 pm
Wow...
Two things
1) Never use Cursors! Looping through a temp table takes less overhead than a Cursor; especially when you are working on a 24/7/365 server where you really need to watch your resources.
2) Don't write code like this:
IF @ListThisNumber = 0
SET @PhoneNumber = '***********'
SET @AllPhones = @AllPhones + @PhoneNumber + ' & '
Doing it this way uses one less variable that you have to Declare and Set:
SELECT @AllPhones = @AllPhones +
CASE
WHEN @ListThisNumber = 0 THEN '***********'
ELSE @PhoneNumber
END + ' & '
– Albert Einstein
January 1, 2009 at 9:23 pm
There are times to use them. I'm jaded because I work on a product where a lot of C developers wrote the database originally. There is dynamic sql and cursors everywhere, even in triggers. They thought in loops instead of sets. I've seen cursors where you loop to call procs. In those cases, I just rewrote the procs to handle a set with a temp table. Make the temp table in the calling parent, child knows its there so when it returns to the parent you have your updated set. Sometimes the procs would just to query a table to get the status of things, so I just decentralized the code a bit. Many times you would do this 4000 times, where I only have to call the proc once. Performance was improved 100 to 1. I think you need to always try to solve set based first. Cursors are easy for programmers to wrap there heads around so in a I needed this done yesterday environment that wins out and when the product is released and the customer complains on performance you revisit it.
January 1, 2009 at 10:47 pm
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
January 2, 2009 at 12:27 am
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
January 2, 2009 at 3:15 am
Extending the table structure given in the article[/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)
abc1 281 444 5555 ,
abc55 11 4582 2752 ,
abc*********** ,
def1 XXX XXX XXXXX ,
-----------------------------------
But i want a result like this
---------------------------------
codName (no column name)
abc1 281 444 5555 , 55 11 4582 2752 , *********** ,
def1 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
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
My question is, Is there any work around for this? (Without using cursors or CTE)
January 2, 2009 at 3:54 am
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.
January 2, 2009 at 5:00 am
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.....
Viewing 15 posts - 1 through 15 (of 87 total)
You must be logged in to reply to this topic. Login to reply