Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
UpsideDownTire
Starting Member
6 Posts |
 Posted - 2010-09-16 : 13:19:47
|
Good morning,I am hearing time and time again that using CURSORs is not efficient, they're slow and cumbersome. Fair enough, I'll give this a try. I want to see if one of my stored procedures can be converted in the hopes that it will then run faster. It is very slow in running.I have a table with record of events. Each record includes the duration of the event in minutes. Like modern telephone bills, the first x minutes are not billed and the minutes thereafter are billed. Of course x can be a different value depending on the client and their retainer on record. And, as typical, the minutes used are the first minutes to consume a retainer. Some events are never billed.I thought the only way to do this accurately was to load a cursor of all the client's events in chronological order and move from first event to last. Since each event has a unique ID, the procedure would update the record in the table (not the cursor) to clear the final billed price for the event as needed.Here's the table:- event_id -- IDENTITY; used in the stored procedure
- client_id -- BIGINT
- client_type -- BIGINT
- datetime
- minutes -- DECIMAL(18,1)
- type -- INT
- non_billable -- INT; flag; some events are never billed, they are various values found in 'type' field
- rate -- DECIMAL(18,2); per hour rate
- price_before -- DECIMAL(18,2); the calculated price before comparison to 'type' and retainer.
- price_after -- DECIMAL(18,2); the calculated price after comparison to retainer. this is printed on the client's bill.
There is a lot of special logic based upon the 'type' field. Some types are never billed depending also on the 'client_type' field. I figured a cursor in a stored procedure was the only way to accomplish this task.When is it ever best to use cursors?Thank you in advance.UDT |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-09-16 : 13:23:31
|
| what all information you want retrive as your output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
UpsideDownTire
Starting Member
6 Posts |
Posted - 2010-09-16 : 13:33:12
|
| There's no output really. It's all about updating records in the original table. Before the procedure is called, the 'price_after' field is equal to the 'price_before' field. The purpose of the stored procedure is to clear the 'price_after' field in selected records. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-09-17 : 12:04:11
|
| whats the rule for clearing the price_after tables?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Replace CURSOR within Stored Procedure