CODEWITHSUNDEEP
sql-serversql-server-2008cursor
zhands
zhands

Reputation: 13

SQL Server 2008 R2: Odd Cursor Behavior

I had to quickly load some data into a database from a spreadsheet and needed to use a cursor. The cursor had some strange side effects and I'm not completely sure why this happened. If anyone can explain it I would appreciate it.

When using an order by clause, the cursor ran 324 times (the correct number of times for my data set). Without the order by, the cursor only ran 81 times. It almost seems like the select statement was re-evaluated for the cursor when the survey Id changed without the select statement.

Here's the code:

declare t cursor for
    SELECT SurveyID, attributename, attributevalue FROM tempCAVImport cav
    left outer join activitylog al on al.entityid = cav.surveyid and al.systemactivitytypeid = 19
    where al.activitylogid is null
    order by cav.surveyid  --this was not in the statement originally
open t

fetch next from t
into @surveyId, @name, @value
declare @count int = 0
while @@Fetch_Status = 0
Begin
        delete from @additionalInfo

        insert into @additionalInfo (AttributeName, AttributeValue, EntityId) 
         select 'ClientAttributeName', @name, @surveyId 

        insert into @additionalInfo (AttributeName, AttributeValue, EntityId) 
         select 'ClientAttributeValue', @value, @surveyId 

        EXEC [InsertActivityLogEntry] null, 'Assigned Extended Attribute', 'Survey', @surveyId, null, @additionalInfo
        set @count = @count + 1
        print(@count)
fetch next from t
into @surveyId, @name, @value
End

close t
deallocate t

Some of the data:

SurveyID    AttributeName   AttributeValue
11545575    Contacted Since Delivery    NO
11545575    Finance Used    xxx
11545575    Recommend Dealer    NEUTRAL
11545575    Recommend xxx   NEUTRAL
11545575    Recommend Finance   NEUTRAL
11545575    Unresolved Problems NO
11626821    Contacted Since Delivery    YES
11626821    Finance Used    xxx
11626821    Recommend Dealer    PROMOTER
11626821    Recommend xxx   PROMOTER
11626821    Recommend Finance   NEUTRAL

Upvotes: 1

Views: 52

Answers (1)

citywall
citywall

Reputation: 235

Your cursor is selecting from a table that the code looping the cursor is inserting into - given that [InsertActivityLogEntry] inserts into the table activitylog that the cursor is joining with.

That is bound to give you trouble, unless you control the order somehow ...

Maybe that's why order by is helping you out :)

Upvotes: 1

Related Questions