Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

When not to use a cursor

Hi I have a script that the only logical method I can use to resolve is a cursor but have been told to NEVER use a cursor. I need to compare one row of data to the previous one and mark the row as OK or not depending on various criteria. Can someone please point me in the right direction as to where to look for a resolution that isn't a cursor. I have a table of visits, the fields are: select startdate, owner, org, customer, customerid, dateapproved, reasonnotpaid from visits I have another table called customers and in here I have a field called NoVisits, what I have to do is get the script to go through the table of visits and update the number of visits in the customers table and update the dateapproved field in the visits table, when it hits the maximum (held in the customers table as SessionsEligible) it should then update the reasonnotpaid field in the visits table to "maximum sessions reached". This is the tables used: CREATE TABLE [import].[Visits]( [StartDate] [datetime] NULL, [OWNER] [varchar](255) NULL, [Org] [varchar](255) NULL, [Customer] [varchar](255) NULL, [CustomerID] [varchar](255) NULL, [Status] [varchar](255) NULL, [DatePaid] [datetime] NULL, [ReasonNotPaid] [varchar](250) NULL, [DateUpdated] [datetime] NULL, [Approved] [varchar](10) NULL ) ON [PRIMARY] END CREATE TABLE [import].[customers]( [Full Name] [nvarchar](255) NULL, [CustomerID] int NULL, [SessionsEligible] [int] NULL, [SessionsHeld] [int] NULL ) ON [PRIMARY]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Fatherjack avatar image
Fatherjack answered
How about a self join on the table using a rowid = rowid-1 criteria? Select a.ColA, a.ColB, b.ColA FROM Table1 as a inner join Table1 as b on a.RowID = b.RowID+1 Can you post some table definition(s) or what you have so far so that we can get into more detail? OK, with the extra detail I think you might make better use of a Common Table Expression and the Row_Number() function to find rows where the visit number is over the eligible count and then you can mark that as required, if you have some sort of row ID in the table that you have missed out(?). Very basically the T-SQL would be like: With VisitStats as (Select RowID, ColA, ColB, Row_Number() OVER(Partition by CustomerID ORDER BY StartDate) as r_n, c.SessionsEligible FROM Visits as v INNER JOIN Customers as c on v.CustID = v.CustID ) UPDATE Visits set ReasonNotPaid = 'Max Sessions' FROM VisitStats as vs INNER JOIN Visits as v on (v.RowID = vs.RowID and r_n > SessionsEligible)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
This is exactly what I would recommend. In general, you can almost always find a set based solution. In the rare cases where a set based solution is truly impossible (iterating over files in the file system for instance), then I recommend considering whether SQL is really the appropriate tool for that job. If you truly have a situation where you wish to use SQL and it absolutely must be in a loop, I find generating a temp table and iterating over that is generally more readable and sometimes faster than a cursor.
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.