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 Wednesday, December 31, 2008 9:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:20 AM
Points: 29, Visits: 234
Comments posted to this topic are about the item Cursors for T-SQL Beginners
Post #628357
Posted Thursday, January 1, 2009 3:36 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,406, Visits: 1,400
Good one for beginners. Excellent explanation...


Post #628387
Posted Thursday, January 1, 2009 11:07 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 4,400, Visits: 6,259
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!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #628445
Posted Thursday, January 1, 2009 11:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 24, 2009 3:36 AM
Points: 12, Visits: 43
article is super-duper for beginners... thank you
Post #628451
Posted Thursday, January 1, 2009 11:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #628457
Posted Thursday, January 1, 2009 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:23 AM
Points: 4, Visits: 162
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
Post #628490
Posted Thursday, January 1, 2009 1:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #628497
Posted Thursday, January 1, 2009 7:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:06 PM
Points: 37, Visits: 176
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 @intChildCount int
declare @datFrom datetime
declare @datTo datetime
declare @strFacilityCurrent varchar(255)
declare @strLineCurrent varchar(255)
declare @strLotIDCurrent varchar(255)
declare @strPKCurrent varchar(255)
declare @strFacility varchar(255)
declare @strLine varchar(255)
declare @strLotID varchar(255)
declare @strPK varchar(255)
declare @guidNew uniqueidentifier
declare @strNameValueList varchar(255)
declare @strTripleValueList varchar(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




Post #628558
Posted Thursday, January 1, 2009 8:39 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 24, 2009 2:13 PM
Points: 83, Visits: 16
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 + ' & '





Imagination is more important than knowledge.

– Albert Einstein
Post #628575
Posted Thursday, January 1, 2009 9:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:23 AM
Points: 4, Visits: 162
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.

Post #628585
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse