There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

Question

There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

Viewing 15 posts - 106 through 120 (of 380 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

JKSQL (4/14/2009)
I am also looking forward to part due. I really try not to use cursors, but sometimes get in traps where I can not think of a better solution. anyway I am always looking for geeky stuff like this.

Thanks, hopefully, this series will help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

RBarryYoung (4/14/2009)
Manie Verster (4/14/2009)
...
RJ, I hope you don't mind but I'm going to jump in here. Barry, maybe RJ's requirements is small enough to justify a distribution list but every year twice a year I have to send out invoices and statements via e-mail to an estimated 15000 people for a client of mine. Would you put that in a distribution list? How would you actually handle that? I am anxious to read your response. You see, the particular query you did as an example I would never put in a cursor. It is a bit unfair to use an example like that and say cursors are bad. Steve had an article a couple of days ago about breaking down code in a stored proc/query to make it more readable and editable. Sometimes cursors help you to do just that...
Check out Notification Services. I am no expert in it, but I have heard that it is designed to send out millions of customized emails at the same time.

Only problem with using Notification Services is that it goes away in SQL Server 2008.

Andy DBA SSCommitted Points: 1776 More actions · Answer 3

I too am looking forward to follow-up articles. I'm hoping new techiques are presented in addition to a consolidation of techiques we've already seen in previous SSC articles.

I've been convinced in the past that my looping code was the only solution to a problem I was working on only to be shown a far more efficient set based solution using a tally table or some other technique!

I now border on being an anti-cursor zealot, but I still concede that there are cases where a loop (cursor or otherwise) might make more sense, especially if the anticipated loop counts are low and the cost of loop overhead is insignificant compared to overall job cost.

Which leads to my question for the purists who suggest looping code can always be optimized using a set based approach: What is a more efficient set based approach for the case where a "canned" stored procedure needs to be called multiple times with varying arguments? (assume rewriting the sp is not an option - eg. it's a SQL Server system sp or vendor-provided sp)

I think that's the trickiest part of RJ's sp_send_dbmail question, not so much the "how do you send multiple e-mails from SQL Server?" (Although that can be tricky, too!)

So, for argument's sake, let's take RJ's question a step further and assume we have a set based query that pulls e-mail address, subject line, and message body columns for sending content specific e-mail reports to a dynamically changing set of sales managers. Let's say we send the top 10% in sales each month a message saying something like "Congratulations, Patty O'Furniture. Your region's sales total of $123,456.00 made the top 10 percent this month."

The obvious looping solution is to build a cursor loop on the query and call sp_send_dbmail inside the loop. Stick a fork in it, it's done.

A dynamic SQL technique was posted that builds a string with the requisite set of "sp_send_dbmail" calls and then executes it. (please forgive my ignorance, but is this technique called aggregated execution?)

This technique has drawbacks, however:

1) If the message body or subject line might contain apostrophes, we would have to call the "REPLACE" function which adds overhead.

2) Depending on how the procedure is called, there might be ownership chain permission issues.

3) It's arguable that the code is less readable/maintainable

I'm convinced that in this case a loop is the best solution but hopefully I'll be pleasantly surprised by subsequent posts to the contrary.

Just so it's clear I'm not asking about e-mail, notification services, etc. my question is: Is it possible to avoid loops (efficiently ;-)) when you need to call sp_cant_touch_this(@someargument as varchar (25)) multiple times with a set of changing arguments?

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 4

Andy DBA (4/14/2009)
Just so it's clear I'm not asking about e-mail, notification services, etc. my question is: Is it possible to avoid loops (efficiently ;-)) when you need to call sp_cant_touch_this(@someargument as varchar (25)) multiple times with a set of changing arguments?

One might argue that either

a) sp_cant_touch_this really should be touched - i.e. the design of the system needs looking at

or

b) sp_cant_touch_this is something that would better be handled by an application rather than some T-SQL.

I personally think b) applies in the case of the email question. I'm sure i'll be hung, drawn & quartered for that opinion however!

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

I'll just jump in and risk making a fool of myself.

In one of my data import apps (Oracle to SQL Server), I use temp tables with varchar(500) fields to ensure all data can be imported successfully to the local server (via DTS). The data from the temp tables is then merged into the final tables using T-SQL (delete / insert) statements.

Since the source data can sometimes contain "invalid data" (e.g. primary key violations or field size too big), the import sometimes fails and this is where my cursor solution comes into play. It imports all rows from the temp table into the final table one by one. Each row that fails is imported into the corresponding error table. This allows correction or just validation of the invalid data lateron and ensures that correct data is available to all clients that were not affected. (Note: the first try is always set based - only in case of an error, the cursor based approach starts)

Wondering if someone has a hint on a better way to do this.

If you need more rationale for this, let me know.

Best Regards,

Chris Büttner

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Lynn Pettis (4/14/2009)
RBarryYoung (4/14/2009)
Manie Verster (4/14/2009)
...
RJ, I hope you don't mind but I'm going to jump in here. Barry, maybe RJ's requirements is small enough to justify a distribution list but every year twice a year I have to send out invoices and statements via e-mail to an estimated 15000 people for a client of mine. Would you put that in a distribution list? How would you actually handle that? I am anxious to read your response. You see, the particular query you did as an example I would never put in a cursor. It is a bit unfair to use an example like that and say cursors are bad. Steve had an article a couple of days ago about breaking down code in a stored proc/query to make it more readable and editable. Sometimes cursors help you to do just that...
Check out Notification Services. I am no expert in it, but I have heard that it is designed to send out millions of customized emails at the same time.
Only problem with using Notification Services is that it goes away in SQL Server 2008.

My understanding was that it's functionality would get rolled into Reporting Services?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Christian Buettner (4/14/2009)
I'll just jump in and risk making a fool of myself.
In one of my data import apps (Oracle to SQL Server), I use temp tables with varchar(500) fields to ensure all data can be imported successfully to the local server (via DTS). The data from the temp tables is then merged into the final tables using T-SQL (delete / insert) statements.
Since the source data can sometimes contain "invalid data" (e.g. primary key violations or field size too big), the import sometimes fails and this is where my cursor solution comes into play. It imports all rows from the temp table into the final table one by one. Each row that fails is imported into the corresponding error table. This allows correction or just validation of the invalid data lateron and ensures that correct data is available to all clients that were not affected. (Note: the first try is always set based - only in case of an error, the cursor based approach starts)
Wondering if someone has a hint on a better way to do this.
If you need more rationale for this, let me know.

I use BULK INSERT, BCP or Integration Services(SSIS) for stuff like this. They have this capability built-in.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

OCTom SSChampion Points: 11785 More actions · Answer 8

Thanks for the series Barry. I'm looking forward to learning this.

bruce.trimpop SSC Veteran Points: 249 More actions · Answer 9

bruce.trimpop (4/14/2009)
Matt Whitfield (4/14/2009)
bruce.trimpop (4/14/2009)
Unfortunately, the "readily consumable format" is a problem. Packaging something up that would cover all or most of the critical functional variations would be a bit time consuming. Thanks though.
My point is just that I agree that using a cursor just because it's the easy solution is a bad approach, that doesn't mean that cursors should never be used. I've found that many "older" developers that came from a row based procedural style of data processing (xBase for example...gosh did I just date myself :-D) found that cursors fit their logic model more comfortably that set based logic. As such I've run into quite a bit of SQL code that used cursors when set based solutions are obviously the better choice. Those fall under the "cursor bad!" category. I've also run into situations where using a cursor is a proper fit, just one more tool in the tool box as it were.
That's a shame - sounded like it could have been an excellent demonstration because of the complexities involved. Using things like windowed functions would probably have given a really good example of how SS2K5 has helped in the eradication of cursors.
If you do feel inclined to post a bit of DDL/DML to set up a test, would be *sooo* appropriate right here 🙂
I'll see if I can come up with something stripped down, but meaningful. I'd love to see some alternate (non-cursor) solutions for this 🙂 I admit I have not revisited this in SS2K5 since it still needs to work in SS2K and it's not a high priority. It may take a day or two to come up with something if folks can wait that long!

Well here it is as best I can manage, I've tried to strip it down to the bare minimum and still keep the complexity!

I apologize for any typo's or messed up data, I worked this up pretty quickly!

:crazy:

I'll be very interested in seeing any non-cursor based solutions.

Thanks all! 🙂

CREATE TABLE [dbo].[client](

[clientid] [int] not null,

[income] [numeric] (12,2) )

CREATE TABLE [dbo].[sometable](

[clientid] [int] not null,

[dependents] [int] )

CREATE TABLE [dbo].[clientfee](

[uniqueid] [int] NOT NULL,

[clientid] [int] not null,

[psfeetablemstrid_c] [char](18) NULL,

[clientfeetype_c] [char](1) NOT NULL CONSTRAINT [df_clientfee1] DEFAULT ('M'),

[feetype_c] [char](1) COLLATE NULL,

[feebasis_n] [numeric](11, 3) NOT NULL CONSTRAINT [df_clientfee2] DEFAULT (0),

[startdate_d] [datetime] NULL,

[enddate_d] [datetime] NULL,

[visitsmax_n] [numeric](10, 0) NOT NULL CONSTRAINT [df_clientfee3] DEFAULT (0),

[visitsused_n] [numeric](10, 0) NOT NULL CONSTRAINT [df_clientfee4] DEFAULT (0),

[dollarsmax_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_clientfee5] DEFAULT (0),

[dollarsused_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_clientfee6] DEFAULT (0),

CONSTRAINT [pk_clientfee] PRIMARY KEY NONCLUSTERED

(

[uniqueid] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

This table (clientfee) can contain multiple rows for a client

clientid is the client id

psfeetablemasterid_c is the unique id of the fee table used if the clientfeetype_c is "T"able based

clientfeetype_c determines if the fee is "M"anual (user entered value) or a "T"able (calculated from user entered criteria)

feetype_c identifies the fee as a "F"lat dollar amount or "P"ercentage of charge

feebasis_n is used when clientfeetype_c is "M"anual and contains a "F"lat dollar amount or a "P"ercent value based on feetype_c

startdate_d and enddate_d is the date range that the fee is valid for.

visitsmax_n and dollarsmax_n are the capitations available for this fee (0 means no cap)

visitsused_n and dollarsused_n track how much of this fee has been used.

=============================================================================

CREATE TABLE [dbo].[psfeetablemstr](

[uniqueid] [int] NOT NULL,

[table1_vc] [varchar](20) NULL,

[column1_vc] [varchar](20) NULL,

[table2_vc] [varchar](20) NULL,

[column2_vc] [varchar](20) NULL,

[feetype_c] [char](1) NULL,

[startdate_d] [datetime] NULL,

[enddate_d] [datetime] NULL,

CONSTRAINT [pk_psfeetablemstr] PRIMARY KEY NONCLUSTERED

(

[uniqueid] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

This table (psfeetablemstr) is contains the master (parent) fee table rows

table1_vc - contains name of user table with column used in range determination

column1_vc - contains name of column in table entered in table1_vc used in range determination

table2_vc - contains name of user table with column used in range determination

column2_vc - contains name of column in table entered in table2_vc used in range determination

feetype_c identifies the fee as a "F"lat dollar amount or "P"ercentage of charge

startdate_d and enddate_d is the date range that the fee table is valid for.

========================================================

CREATE TABLE [dbo].[psfeetable](

[uniqueid] [int] NOT NULL,

[value1min_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable1] DEFAULT (0),

[value1max_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable2] DEFAULT (0),

[value2min_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable3] DEFAULT (0),

[value2max_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable4] DEFAULT (0),

[value3_n] [numeric](11, 3) NOT NULL CONSTRAINT [df_psfeetable5] DEFAULT (0),

[psfeetablemstrid] [int] NOT NULL,

CONSTRAINT [pk_psfeetable] PRIMARY KEY NONCLUSTERED

(

[uniqueid] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

This table (psfeetable) contains the child fee table rows that are linked to psfeetablemstr

value1min_n minimum value from the column stored in column 1 stored in psfeetablemastr to compare against

value1max_n maximum value from the column stored in column 1 stored in psfeetablemastr to compare against

value2min_n minimum value from the column stored in column2 stored in psfeetablemastr to compare against

value2max_n maximum value from the column stored in column2 stored in psfeetablemastr to compare against

value3_n contains the fee value for the this range

psfeetablemstrid links the row to the appropriate fee table in psfeetablemstr

========================================================

CREATE TABLE [dbo].[service](

[uniqueid] [int] NOT NULL,

[servicedate_d] [datetime] NOT NULL,

[component1_c] [char](5) NULL,

[component2_c] [char](5) NULL,

[component3_c] [char](5) NULL,

[component4_c] [char](5) NULL,

[component5_c] [char](5) NULL,

[component6_c] [char](20) NULL,

[component7_c] [char](20) NULL,

[component8_c] [char](20) NULL,

[component9_c] [char](20) NULL,

[component0_c] [char](20) NULL,

[clientid] [int] NULL,

[charge] [numeric] (12,2) NULL,

[calculatedfee] [numeric] (12,2) null

CONSTRAINT [pk_activwork] PRIMARY KEY NONCLUSTERED

(

[uniqueid] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

This table contains the client services

servicedate_c is the date of service that will be compared to the start/end dates in the fee tables.

component0_c through component9_c are the service components that determine what the service is at least one component must have a value.

clientid is the id of client who recieved service used to link the clientfee table

=====================================================

CREATE TABLE [dbo].[limitcodes](

[uniqueid] [int] NOT NULL,

[codetype_c] [char](10) NOT NULL,

[validcode_vc] [varchar](20) NOT NULL,

[startdate_d] [datetime] NULL,

[enddate_d] [datetime] NULL,

[source_c] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[linkid_c] [char](18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [pk_limitcodes] PRIMARY KEY NONCLUSTERED

(

[uniqueid_c] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Contains service codes that various things are valid for

codetype_c contains which component column the code is entered in.

validcode_vc contains the valid service code

start/end dates are the date range the code is valid for

source_c is the table that has the service validation criteria entered against

linkid_c is the uniqueid of the row from the table in source_c that has the service validation criteria entered against

Sample data

dbo.client

1,25000

dbo.sometable

1,2

dbo.clientfee

1,1,1,"T",NULL,NULL,4/1/2009,NULL,30,29,50,400

2,1,1,"T",NULL,NULL,1/1/2009,3/31/2009,20,20,300,300

3,1,0,"M","P",25,1/1/2009,NULL,50,45,0,0

4,1,0,"M","F",10,1/1/2009,NULL,0,0,100,0

5,1,2,"T",NULL,NULL,1/1/2009,0,0,0,0

dbo.psfeetablemstr

1,"dbo.client","income","dbo.sometable","dependents","P",NULL,NULL

2,"dbo.sometable","dependents","F",NULL,NULL

dbo.psfeetable

1,0,5000,1,1,10,1

2,5001,10000,1,1,20,1

3,10001,20000,1,1,30,1

4,20001,50000,1,1,40,1

5,50001,9999999,1,1,50,1

6,0,5000,2,3,7,1

7,5001,10000,2,3,17,1

8,10001,20000,2,3,27,1

9,20001,50000,2,3,37,1

10,50001,9999999,2,3,47,1

11,0,5000,4,999,5,1

12,5001,10000,4,999,15,1

13,10001,20000,4,999,25,1

14,20001,50000,4,999,35,1

15,50001,9999999,4,999,45,1

16,1,1,0,0,10,2

17,2,3,0,0,20,2

18,4,999,0,0,30,2

dbo.service

1,3/1/2009,"A",NULL,NULL,"B",NULL,NULL,NULL,NULL,NULL,NULL,1,100.00,NULL

2,4/1/2009,"A",NULL,NULL,"B",NULL,NULL,NULL,NULL,NULL,NULL,1,100.00,NULL

3,5/1/2009,"A",NULL,NULL,"B",NULL,NULL,NULL,NULL,NULL,NULL,1,100.00,NULL

4,3/1/2009,"A","C",NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,200.00,NULL

5,3/1/2009,NULL,"1",NULL,NULL,"D",NULL,NULL,NULL,NULL,NULL,1,200.00,NULL

6,4/1/2009,NULL,"2",NULL,NULL,"D",NULL,NULL,NULL,NULL,NULL,1,150.00,NULL

7,5/1/2009,NULL,"3",NULL,NULL,"D",NULL,NULL,NULL,NULL,NULL,1,150.00,NULL

dbo.limitcodes

1,"component1_c","A",NULL,NULL,'psfeetablemstr',1

2,"component1_c","B",NULL,NULL,'psfeetablemstr',1

3,"component1_c","C",NULL,NULL,'psfeetablemstr',1

4,"component4_c","B",NULL,NULL,'psfeetablemstr',1

5,"component4_c","D",NULL,NULL,'psfeetablemstr',1

6,"component2_c","1",NULL,NULL,'psfeetablemstr',2

7,"component2_c","2",NULL,NULL,'psfeetablemstr',2

8,"component5_c","D",NULL,NULL,'psfeetablemstr',2

9,"component5_c","D",NULL,NULL,'clientfee',4

Note - limitcodes explained - this means that the psfeetablemstr (fee table) with id 1 is only valid for services

where component1 is "A","B", or "C" AND component4 is "B" or "D". psfeetablemstr (fee table) with id 2 is only valid for services where

component2 is "1" or "2" AND component5 is "D". clientfee row with id 4 is only valid for services where component5 is "D"

So given this data the services would end up with the following calculatedfee values for the services based on the charge values stored with the service

dbo.service

1. $25 (clientfee row 3 manual 25 percent used)

2. $37 (clientfee row 1, psfeetablemstr row 1, psfeetable row 9 because client.income and sometable.dependents values fall in this range)

3. $25 (clientfee row 3 because previous service used remaining visits cap of fee table 1

4. $50 (clientfee row 3 only fee that covers component2 = "C" because it has not service limitations)

5. $20 (clientfee row 5 psfeetablemstr row 2 psfeetable row 17 flat $20 because sometable.dependents value falls in this range)

6. $20 (clientfee row 5 psfeetablemstr row 2 psfeetable row 17 flat $20 because sometable.dependents value falls in this range)

7. $10 (clientfee row 4 only fee that covers component5 = "D" regardless of other component values)

I300I SSC Eights! Points: 828 More actions · Answer 10

The author makes the claim several times that there is no reason to ever use a cursor again with SQL Server 2005, because everything can be done with set-based logic. When the very realistic question is posed about calling sp_send_dbmail, we see no answer that supports the claim. Notification Services?, SQL CLR?, RS? These are the solutions to avoid a simple looping process to send emails?

I am interested in seeing this problem addressed without a looping process:

I ship a packaged DB product to our customers. I do not use NS or SQL CLR. I am not on site to administer their systems.

I have a set of 50 pre-defined status messages in a message table. Each row in the message table defines a message, with all the execution parameters for a call to sp_send_dbmail. A message execution schedule is also included, as well as the last execution date/time, and results of the last sp_send_dbmail procedure execution for each message.

The stored procedure is kicked-off by an hourly scheduled job, but only those messages that are due to be sent are sent. The job executes in less than 5 seconds.

Sure, I can avoid a cursor by creating a temp table, loading all the rows that are due to be sent, WHILE-LOOPING through each row to send a message and capture the proc result, then updating my message table with the results. This is a lot more code than using a basic fast_forward cursor, and there is no performance issue with such a small amount of work to do.

RJ-476420 SSCarpal Tunnel Points: 4806 More actions · Answer 11

Great example, RJ. Of course, A specific answer will be dependent on the specifics details of your requirements. For instance is "msdb.dbo.sp_send_dbmail" really a business requirement or is it just assumed to be the means that you will have to use?
General speaking I can see 4 possible ways to approach this that do not use Cursors: distribution lists, NS (Notification Services), SQL CLR (or external activation), and aggregated execution. Distribution lists are usually the preferred solution.

Well! After a long day I got a chance to read all the posts. WOW!!!

I think you all misplaced my requirement. The job (which is a stored procedure) will identify users who can continue to be an valid user in my application. If for some reason the user no longer qualify to be an active user in the application then the user would be deleted from the application.

Once this happens the user & his/her manager would be notified that the user has been removed from the system. when the job runs there could be multiple users removed from the system & each user might have one or more managers associated i.e.>

user1 -> manager1

user2 -> manager2, manager3

user3 -> manager1, manager4

& so on....

The schema structure is below:

create table user (id int, username varchar(100), email varchar(100), status int)

create table usermanager (id int, deptid int)

create table userdept (id int, deptid int)

alter table usermanager add constraint user_usermanager_fk

foreign key (id) references user(id)

alter table usermanager add constraint dept_usermanager_fk

foreign key (deptid) references dept(id)

alter table userdept add constraint user_userdept_fk

foreign key (id) references user(id)

alter table userdept add constraint dept_userdept_fk

foreign key (deptid) references dept(id)

insert into user (id,username,email,status)

select 1,'user1','user1@user.com',0

union all

select 2,'user2','user2@user.com',0

union all

select 3,'user3','user3@user.com',0

union all

select 4,'user4','user4@user.com',0

insert into usermanager (id,deptid)

select 2,1

insert into userdept (id,deptid)

select 1,1

union all

select 3,1

union all

select 4,1

In the above code user2 is the manager for users 1,3 &4. If any users status changes from 0 to 1 then they should be deleted from the system & email to both user & manager should be sent.

I can only use stored procedure as far as my knowledge & cannot use any other approach like NS (can't use it as we would be moving to SQL Server 2008 shortly), distribution lists (wow what is distribution lists?), SQL CLR (I got to do some research myself), and aggregated execution (well I dont understand this part either)

Thanks

RJ

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 12

[font="Verdana"]An interesting start, and I'll look forward to reading more.

BTW, I would challenge the statement that cursors perform better in Oracle. From my own experience (from Oracle 7.0 through to Oracle 10g), cursors perform just as poorly in Oracle as they do in SQL Server. Technology like Oracle Forms and Oracle Reports inherently create cursors, so they are prevalent: that doesn't mean they perform well.

In terms of the example of e-mailing out using a cursor, my own thoughts are:

1. Yes, I've done the same. Admittedly I used a while loop without a cursor. But same thing.

2. I believe that Notification Services (part of Reporting Services in 2008) is designed to do this sort of thing. Might be a reason to upgrade from SQL Server 2000! However, I haven't played around with it myself. Worth investigating though if your system relies on this.

3. Is sending e-mail from the database the right approach? I've done it for emailed alerts to DBAs or the like, but I'm thinking from an architectural and security point of view for an application, it would be better to handle this outside the database.

4. Converting it to one huge piece of dynamic SQL won't work. SQL Server 2000 doesn't have varchar(max).

I believe that there are times when cursors are appropriate, but as SQL Server and Oracle develop, I have found myself using them less and less.

[/font]

Jorge Vinuales Valued Member Points: 51 More actions · Answer 13

Jorge Vinuales

Valued Member

Points: 51

April 14, 2009 at 3:36 pm

#977718

Joe Celko????

Is it possible??

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 14

bruce.trimpop (4/14/2009)

Bruce

Here is the SQL i used on 2000 to re-create the data and tables - some mods were required, so hope you can check.

Is it possible that you could post the cursor you use? I am having trouble understanding how some of the data interacts, and seeing the source would really help.

My initial reaction would be that the table layout would benefit from a bit of a redesign in terms of being somewhere close to 3NF, which would probably have made the set based approach a lot easier in the first place. I presume that you cannot change table structures however, and will respect that in my solution!

The code...

CREATE TABLE [dbo].[client](

[clientid] [int] not null,

[income] [numeric] (12,2)

)

CREATE TABLE [dbo].[sometable](

[clientid] [int] not null,

[dependents] [int]

)

CREATE TABLE [dbo].[clientfee](

[uniqueid] [int] NOT NULL,

[clientid] [int] not null,

[psfeetablemstrid_c] [char](18) NULL,

[clientfeetype_c] [char](1) NOT NULL CONSTRAINT [df_clientfee1] DEFAULT ('M'),

[feetype_c] [char](1) NULL,

[feebasis_n] [numeric](11, 3) NOT NULL CONSTRAINT [df_clientfee2] DEFAULT (0),

[startdate_d] [datetime] NULL,

[enddate_d] [datetime] NULL,

[visitsmax_n] [numeric](10, 0) NOT NULL CONSTRAINT [df_clientfee3] DEFAULT (0),

[visitsused_n] [numeric](10, 0) NOT NULL CONSTRAINT [df_clientfee4] DEFAULT (0),

[dollarsmax_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_clientfee5] DEFAULT (0),

[dollarsused_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_clientfee6] DEFAULT (0),

CONSTRAINT [pk_clientfee] PRIMARY KEY NONCLUSTERED

( [uniqueid] ASC )

)

CREATE TABLE [dbo].[psfeetablemstr](

[uniqueid] [int] NOT NULL,

[table1_vc] [varchar](20) NULL,

[column1_vc] [varchar](20) NULL,

[table2_vc] [varchar](20) NULL,

[column2_vc] [varchar](20) NULL,

[feetype_c] [char](1) NULL,

[startdate_d] [datetime] NULL,

[enddate_d] [datetime] NULL,

CONSTRAINT [pk_psfeetablemstr] PRIMARY KEY NONCLUSTERED

( [uniqueid] ASC )

)

CREATE TABLE [dbo].[psfeetable](

[uniqueid] [int] NOT NULL,

[value1min_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable1] DEFAULT (0),

[value1max_n] [numeric](12, 2) NOT NULL CONSTRAINT [df_psfeetable2] DEFAULT (0),

[value2min_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable3] DEFAULT (0),

[value2max_n] [numeric](12, 2) NULL CONSTRAINT [df_psfeetable4] DEFAULT (0),

[value3_n] [numeric](11, 3) NOT NULL CONSTRAINT [df_psfeetable5] DEFAULT (0),

[psfeetablemstrid] [int] NOT NULL,

CONSTRAINT [pk_psfeetable] PRIMARY KEY NONCLUSTERED

( [uniqueid] ASC )

)

CREATE TABLE [dbo].[service](

[uniqueid] [int] NOT NULL,

[servicedate_d] [datetime] NOT NULL,

[component1_c] [char](5) NULL,

[component2_c] [char](5) NULL,

[component3_c] [char](5) NULL,

[component4_c] [char](5) NULL,

[component5_c] [char](5) NULL,

[component6_c] [char](20) NULL,

[component7_c] [char](20) NULL,

[component8_c] [char](20) NULL,

[component9_c] [char](20) NULL,

[component0_c] [char](20) NULL,

[clientid] [int] NULL,

[charge] [numeric] (12,2) NULL,

[calculatedfee] [numeric] (12,2) null

CONSTRAINT [pk_activwork] PRIMARY KEY NONCLUSTERED

( [uniqueid] ASC )

)

CREATE TABLE [dbo].[limitcodes](

[uniqueid] [int] NOT NULL,

[codetype_c] [char](20) NOT NULL,

[validcode_vc] [varchar](20) NOT NULL,

[startdate_d] [datetime] NULL,

[enddate_d] [datetime] NULL,

[source_c] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[linkid_c] [char](18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [pk_limitcodes] PRIMARY KEY NONCLUSTERED

( [uniqueid] ASC )

)

--Sample data

--dbo.client

INSERT INTO [dbo].[client] ([clientid], [income])

VALUES (1,25000)

--dbo.sometable

INSERT INTO [dbo].[sometable] ([clientid], [dependents])

VALUES (1,2)

--dbo.clientfee

INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid_c], [clientfeetype_c], [feetype_c], [feebasis_n], [startdate_d], [enddate_d], [visitsmax_n], [visitsused_n], [dollarsmax_n], [dollarsused_n])

VALUES (1,1,1,'T',NULL,0,'20090401',NULL,30,29,50,400)

INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid_c], [clientfeetype_c], [feetype_c], [feebasis_n], [startdate_d], [enddate_d], [visitsmax_n], [visitsused_n], [dollarsmax_n], [dollarsused_n])

VALUES (2,1,1,'T',NULL,0,'20090101','20090331',20,20,300,300)

INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid_c], [clientfeetype_c], [feetype_c], [feebasis_n], [startdate_d], [enddate_d], [visitsmax_n], [visitsused_n], [dollarsmax_n], [dollarsused_n])

VALUES (3,1,0,'M','P',25,'20090101',NULL,50,45,0,0)

INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid_c], [clientfeetype_c], [feetype_c], [feebasis_n], [startdate_d], [enddate_d], [visitsmax_n], [visitsused_n], [dollarsmax_n], [dollarsused_n])

VALUES (4,1,0,'M','F',10,'20090101',NULL,0,0,100,0)

INSERT INTO [dbo].[clientfee] ([uniqueid], [clientid], [psfeetablemstrid_c], [clientfeetype_c], [feetype_c], [feebasis_n], [startdate_d], [enddate_d], [visitsmax_n], [visitsused_n], [dollarsmax_n], [dollarsused_n])

VALUES (5,1,2,'T',NULL,0,'20090101',NULL,0,0,0,0)

--dbo.psfeetablemstr

INSERT INTO [dbo].[psfeetablemstr] ([uniqueid], [table1_vc], [column1_vc], [table2_vc], [column2_vc], [feetype_c], [startdate_d], [enddate_d])

VALUES (1,'dbo.client','income','dbo.sometable','dependents','P',NULL,NULL)

INSERT INTO [dbo].[psfeetablemstr] ([uniqueid], [table1_vc], [column1_vc], [table2_vc], [column2_vc], [feetype_c], [startdate_d], [enddate_d])

VALUES (2,'dbo.sometable','dependents',NULL,NULL,'F',NULL,NULL)

--dbo.psfeetable