x

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]


more ▼

asked Aug 24, 2010 at 07:49 AM in Default

avatar image

Mrs_Fatherjack
5.2k 66 69 77

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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)

more ▼

answered Aug 24, 2010 at 07:58 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

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.

Aug 24, 2010 at 09:09 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2198
x1091
x70
x4

asked: Aug 24, 2010 at 07:49 AM

Seen: 1229 times

Last Updated: Aug 24, 2010 at 09:08 AM

Copyright 2018 Redgate Software. Privacy Policy