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
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
Comments posted to this topic are about the item Cursors for T-SQL Beginners
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6275 Visits: 1407
Good one for beginners. Excellent explanation...



TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5937 Visits: 8298
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 at GMail
eramya
eramya
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 43
article is super-duper for beginners... thank you
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44981 Visits: 39869
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gryphonsclaw
gryphonsclaw
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 178
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44981 Visits: 39869
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JRoughgarden
JRoughgarden
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 195
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. Wink

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





battelofhalfwits
battelofhalfwits
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 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

gryphonsclaw
gryphonsclaw
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 178
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.
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