April 14, 2009 at 10:48 am
RJ (4/14/2009)
I would like to see how you can eliminate using a CURSOR or WHILE loop for sending emails using "msdb.dbo.sp_send_dbmail". My requirement would be to send email notifications to managers whenever there was an action taken against users (not database or server level users they are custom users created by the software application) specific to the application.
This is a nightly job that pulls all users who satisfy few conditions and users & managers are notified by emails. How do I eliminate a looping functions to send emails?
Thanks
RJ
I usually do something like this
declare @Error_Status int, @Row_Count int
declare @Error_Message varChar (128)
set @Error_Status = 0
set @Error_Message = ''
/******************************************************************************************
The Query
******************************************************************************************/
select * from someTable
select @Error_Status = @@Error, @Row_Count = @@RowCount
if @Error_Status <> 0 -- or @Row_Count !=1 (or) = 0 --which ever is Appropriate
Begin
set @Error_Message = 'Something apposite'
raiserror(@Error_Message, 16, 1)
goto ErrorHandler
End
return @Error_Status --should be Zero at this point
/**********************************ErrorHandler*******************************************/
ErrorHandler:
declare @sql varChar(8000)
declare @mailMessage varChar(128)
declare @mailSubject varChar(128)
declare @Procedure_Name sysName -- Holds the name of the currently executing procedure
-- Set the procedure name in the variable
set @Procedure_Name = Object_Name(@@ProcID)
if Object_Id('AlertEmail_T') is Null
return @Error_Status
declare @recipients varChar(4000)
select @recipients = COALESCE(@recipients+';' , '') + emailAddress from AlertEmail_T where sendMail = 1
/*
This sends the message
*/
set @mailMessage = 'Failure involving ' --something apposite
set @mailSubject = 'Error in ' + isNull(@Procedure_Name, 'Ad-hoc Query')
set @sql = 'exec master.dbo.xp_sendmail ''' + @recipients + '''
,@message = ''' + @mailMessage + '''
,@query = ''select ''''' + @Error_Message + ''''' as ErrorMessage,
' + isNull(Cast(@Error_Status as varChar),' ') + ' as ErrorNumber''
,@subject = ''' + @mailSubject + '''
,@dbuse = ''master''
,@attach_results = false
,@no_output = true
,@width = 8000'
--print (@sql) --change this from print to an exec to 'really' do it.
exec (@sql) --or uncomment this line
return @Error_Status
where AlertEmail_T is a table with your recipents addresses and the where clause pulls back the correct people to mail. Note this is a SQL 2000 solution, I have no idea if it works in SQL 2005+. I don't know if the parameters are the same for sp_send_dbmail. Let me know how you get on.
Dave J
April 14, 2009 at 10:49 am
Matt Whitfield (4/14/2009)
sushila (4/14/2009)
As for the instances where you use cursors and no one else would be able to "improve either performance or readability with their removal", I volunteer Jeff Moden (Jeff - hope you don't mind!:-)) - give him a chance to take a look at it and we can all credit you with the intelligence you lay claim to, if Jeff cannot remove them and turbo their pace!I will volunteer too 🙂
Accepted!
[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]
April 14, 2009 at 10:52 am
Steve Rosenbach (4/14/2009)
...In fact, the title of this series inspired me to write new lyrics for the first verse of the great Paul Simon song - Barry, this is for you:My predecesor was a genius, that is true
But unfortunately SQL wasn't what he liked to do
So I'm cursed with code that's waterlogged with cursors through and through, but there's
15 ways to lose your cursors
There must be,
15 ways to lose your cursors.
BWAHAHAHhaha!!! That's great, Steve! I might use that, if you don't mind?
[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]
April 14, 2009 at 10:56 am
"....I might use that, if you don't mind?"
Mind?! I'd be honored!
Best regards,
SteveR
April 14, 2009 at 10:56 am
Lynn Pettis (4/14/2009)
There are several of us who would take on the challenge of attempting to rewrite a cursor based solution as a set based solution. If you take up the SSC community challenge, be sure to provide complete information on what needs to be done, including table definitions, sample data, and expected results based on the sample data. Be sure any code you post is properly formatted, and data readily consumable.
Thanks for the help, Lynn. Although I believe in the correctness of my claim, I also know from experience that converting cursor-based code can be a big deal sometimes.
[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]
April 14, 2009 at 10:59 am
I think the general consensus is: Use sets when you can, cursors when you have to. Now I may be wrong on that but if anyone wants to use cursors, here's a link to help you limit any potential performance issues:
http://www.sql-server-performance.com/tips/cursors_p1.aspx
Cheers!
Gaby A.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
April 14, 2009 at 11:04 am
Lynn Pettis (4/14/2009)
bruce.trimpop (4/14/2009)
Matt Whitfield (4/14/2009)
bruce.trimpop (4/14/2009)
I put forth a real world problem to that person that I could find no way to accomplish without using a cursor. I never heard back from themI don't suppose you remember it do you? I'd love to have a crack at it if you do 🙂
In a nutshell, it was a sliding fee calculation that was based on user selected criteria with capitations involved. The process had to return a customer's fee based on the user entered criteria. For example: the fee might be based on Income from a user's custom table and number of dependents from an HR application table, but the fee might also have a capitation, for example, for the first $500 use this fee if it meets all the criteria, for $501 to $1000 use this fee etc, plus it could also be date range driven and also based on the type of service provided. So an example might be:
Customer makes $20,000 stored in user.table.income and has 3 dependents stored in application.demographic.dependents. Sliding fees might be $0 to $1000 income with 0 dependents $30 fee,
0 - $1000 with 1 or 2 dependents $20 fee, 0 - $1000 with 3 or more dependents $10 fee. $1001 to $5000 with 0 dependents etc etc. with this set of fees valid for services a, b, and c. up to a maximum of $500 or 30 visits. Then you have the next set of sliding fees for services d and e with different ranges and $ values and valid for January thru June. Etc etc etc. Note that the criteria for comparing against any particular sliding fee configuration could change from one to the next. One fee might be based on income and dependents. The next might be income alone, the next by be dependents alone, the next might be some completely other criteria. Believe it or not I have simplified the actual requirements here just so I could fit it in a relatively short post.
I got a headache trying to figure out how to do this "set based". I ended up using a cursor to build dynamic queries. Executing the dynamic query and if I got a result I had my fee, if not, onto the next cursor row.
If you have the DDL for the tables, sample data (in a readily consumable format), and expected results based on the sample data that would be great.
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.
April 14, 2009 at 11:06 am
RBarryYoung (4/14/2009)
Lynn Pettis (4/14/2009)
There are several of us who would take on the challenge of attempting to rewrite a cursor based solution as a set based solution. If you take up the SSC community challenge, be sure to provide complete information on what needs to be done, including table definitions, sample data, and expected results based on the sample data. Be sure any code you post is properly formatted, and data readily consumable.Thanks for the help, Lynn. Although I believe in the correctness of my claim, I also know from experience that converting cursor-based code can be a big deal sometimes.
I'm quite sure of that as well Barry. I have had to convert cursor based solutions as well. Still have some here that aren't critical due to data volumes but i still want to get to them eventually.
April 14, 2009 at 11:11 am
dbishop (4/14/2009)
I must say that for an introduction you did an okay job. The only thing I took offense of was the procedural vs. declarative examples and your definition of procedural languages (VB, C#, Java). I've alsways heard these referred to as event-driven, not procedural.COBOL and Fortran are procedural languages.
"Procedural" is an extremely large class of languages that includes virtually everything except 4GL's/Dataset languages (SQL being the only significant survivor), Logic languages (Prolog, etc.) and Functional languages (Haskell, etc.). "Event-Driven" languages are a sub-class of the procedural languages. "OO" or "Object Oriented" are a different, overlapping subset that also includes VB, C# and Java. Traditional COBOL and Fortran are generally categorized in the subset know as "3GL".
The "old school" definition of Procedural coding is "code that is written in the form of a procedure". This certainly still includes VB, C#, C++, Java, etc. (Note: Wikipedia is using revisionist terminology that would instead call this "imperative programming", but I like the old terms better).
[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]
April 14, 2009 at 11:15 am
john.danley (4/14/2009)
Thanks sushila for your comments so I don't have to. I agree with what you said.I enjoyed the article and am looking forward to seeing the set based solutions to come out of it.
...
So part of the culture I'm trying to instill is for our database developers to get away from cursors and write set-based code. I'm hoping this series will help with this goal. Thanks for the article!
Thanks John, that's my hope too!
[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]
April 14, 2009 at 11:17 am
WayneS (4/14/2009)
Thanks for the great article.I also am waiting for the rest of this series. Hopefully, the wait is just daily.
...
Thanks, Wayne, but I am no Steve Jones! The next installment is currently scheduled for the 27th.
[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]
April 14, 2009 at 11:18 am
Dallas Martin (4/14/2009)
This code is considerably faster:Declare @X int
Set @X = 0
Select @X = @X + 1
From master.sys.columns c1
Cross Join master.sys.columns c2
Print @X
Great example, Dallas!
[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]
April 14, 2009 at 11:21 am
Hi, I am very new to SQL an when I paste the example into Query analyzer
I get the following error
Server: Msg 208, Level 16, State 1, Line 6
Invalid object name 'master.sys.columns'.
What on earth could I have done wrong.....
Thanks
April 14, 2009 at 11:22 am
steve (4/14/2009)
OK, the issue is simply one of coding style for the most part. The optimizer should handle "declarative" code [whatever that is] in much the same way that it would handle cursors or the product [database] is not built right.I write cursors all the time without any appreciable difference from other code. It's all basic procedural programming. There is no declarative and cursor-driven difference.
If you have an example that you can show us, we will do our best to demonstrate the difference that I am talking about.
[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]
April 14, 2009 at 11:23 am
Heather McPherson (4/14/2009)
Hi, I am very new to SQL an when I paste the example into Query analyzerI get the following error
Server: Msg 208, Level 16, State 1, Line 6
Invalid object name 'master.sys.columns'.
What on earth could I have done wrong.....
Thanks
What version of SQL Server are you using? If you are using SQL Server 2000, change master.sys.columns to master.dbo.syscolumns.
The table master.sys.columns is available in SQL Server 2005/2008.
Viewing 15 posts - 76 through 90 (of 380 total)
You must be logged in to reply to this topic. Login to reply