Cursors for T-SQL Beginners

  • Comments posted to this topic are about the item Cursors for T-SQL Beginners

  • Good one for beginners. Excellent explanation...

  • 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

  • article is super-duper for beginners... thank you

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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)

  • 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.

  • 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