Solved

SQL Stored Procedure Loop for Job

Posted on 2009-07-02
20
444 Views
Last Modified: 2012-08-14
Hi.  I am currently struggling with a SQL Procedure where I am attempting to send an email by a SQL Job if database Criteria is met.  Currently, I have the following test syntax, which works fine.

DECLARE @AdditionalFilter BIT
DECLARE @MyDate As smalldatetime
SET @AdditionalFilter = 1
SET @MyDate = convert(varchar(10), ('07/02/2009'))
IF @AdditionalFilter = 1 AND convert(varchar(10), (getdate()), 101) = @MyDate
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'To confirm that you are getting an email on a particular date time "' + '.'
EXEC msdb.dbo.sp_send_dbmail @recipients=N'ME@somewhere.com', @body= @msg,  @subject = 'SQL Server Trigger Mail', @profile_name = 'Profile Name'
SET @AdditionalFilter = 0
PRINT 'Message Sent!'
END
ELSE
BEGIN
PRINT 'No Message Sent'
END

This simply sends a message to one recipient if the date is found to be today.  Simple enough, I will just put this into a SQL Job and be good.  

Problem comes in that I want to insert another criteria so that if in the following SQL Statement:

SELECT Field1, Field2, Field3 from Table1 where Complete = N'No';

All values in the table that are found to have 'No' for their value (It is an nvarchar(4) field, not boolean), will be sent an email via that same job.  So basically I'm asking for a Loop, but I understand that a SQL "Loop" may not be quite what I'm looking for.  I've searched all around and found close to what I'm looking for but not quite enough to get me going.  

I guess I'm looking for possibly a CURSOR statement, or simply something that takes the original procedure and adds values of Field1, Field2, Field3 to the email from records contained in 'Table1' where Complete = 'No'.  If I'm going about anything wrong I apologize, this is still somewhat new to me.

Thank you for any assistance.
0
Comment
Question by:sri-admin
  • 7
  • 3
  • 2
  • +4
20 Comments
 
LVL 15

Accepted Solution

by:
danrosenthal earned 300 total points
ID: 24769079
If I understand correctly, it sounds like a cursor would do it for you.

The comments in this question should point you in the right direction:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23815322.html

If you have specific questions, let me know.
0
 
LVL 39

Expert Comment

by:appari
ID: 24769082
try changing your call to sp_send_dbmail as follows to include the @query parameter,

EXEC msdb.dbo.sp_send_dbmail @recipients=N'ME@somewhere.com', @body= @msg,  @query = 'SELECT Field1, Field2, Field3 from Table1 where Complete = N''No''' ,@subject = 'SQL Server Trigger Mail', @profile_name = 'Profile Name'
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24769089
in SQL 2005, You can also do something like this:
 

DECLARE @tableHTML  NVARCHAR(8000);

DECLARE @AdditionalFilter BIT 

DECLARE @MyDate As smalldatetime

SET @AdditionalFilter = 1

SET @MyDate = convert(varchar(10), ('07/02/2009'))

IF @AdditionalFilter = 1 AND convert(varchar(10), (getdate()), 101) = @MyDate

BEGIN

	DECLARE @msg varchar(500)
 

	SET @tableHTML = N'To confirm that you are getting an email on a particular date time "' + '. <br/><br/>' +

			N'<table border="1">' + 

			N'<tr><th>field 1</th><th>field 2</th><th>field 3</th>' +

			CAST ( ( SELECT td = UserName,       '',

	        			td = sum(case when Review_status = 'Assigned' then 1 else 0 end), '',

	                    		td = sum(case when Review_Status = 'Complete' then 1 else 0 end), ''

        	      		FROM SCH_A

				WHERE Complete = N'No'

	              		FOR XML PATH('tr'), TYPE 

    				) AS NVARCHAR(MAX) ) +

	    		N'</table>'
 
 

	EXEC msdb.dbo.sp_send_dbmail @recipients='yourmailrecipient@email.com',

			@subject = 'SQL Server Trigger Mail', 

			@profile_name = 'Profile Name',

			@body = @tableHTML,

		        @body_format = 'HTML' ;
 
 

	SET @AdditionalFilter = 0

	PRINT 'Message Sent!'

END

ELSE

BEGIN

	PRINT 'No Message Sent'

END

Open in new window

0
 
LVL 39

Expert Comment

by:appari
ID: 24769096
wait, do you want to send one email for each record or all the records together?
if you want to send multiple mails you have to use cursor if one mail is enough you can use @query parameter as in my previous post.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24769121
0
 
LVL 10

Expert Comment

by:Banthor
ID: 24769178
If you really don't want to use a Cursor you can create Function that sends the Mail
then Call the Function as column of the select,
While not getting to detailed here;
Select
  dbo.SendThatMail([Recip],'CompiledMEssage')
From
  Notices where Criteria is Met
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24769188
>> If you really don't want to use a Cursor you can create Function that sends the Mail then Call the Function as column of the select,<<
You may want to double check that.  I suspect you will get the following error:
"Only functions and extended stored procedures can be executed from within a function."
0
 
LVL 19

Assisted Solution

by:folderol
folderol earned 200 total points
ID: 24769212
Here is a loop that will send an email for every row that matches Complete = 'No'

It does not use the cool html stuff that ralmada posted, you could combine the two without much effort.

The key advantage in temp tables is if the number of rows that match the Where is significantly less than the total rows, the performance overhead in using one is not an issue.  You can index the temp table as I do in the snippet, which helps.  The temp table persists for the connection, when the connection ends then the index drops when the table drops.

You can add
@attach_query_result_as_file = 1

to the sp_send_dbmail command to put the query resultset into an attachment, otherwise it appears below the @body text, if any.


declare @nextid int

declare @lastid int

declare @selectcmd nvarchar(255)

CREATE TABLE #tmploop(

	id int identity(1,1) not null,

	key1 nvarchar(100) null,

	key2 nvarchar(100) null

	)

CREATE INDEX[IX_loop_id] ON [#tmploop]([id]) ON [PRIMARY]
 

insert into #tmploop(key1, key2)

select Field1, Field2 from Table1 where Complete = N'No'
 

select @lastid = max(id) from #tmploop

set @nextid = 1
 

while @nextid <= @lastid

BEGIN

  set @selectcmd = 'select field1, field2, field3 from #tmploop join table1 on key1=field1 and key2=field2 where #tmploop.id = ' + cast(@nextid as varchar)

  set @nextid = @nextid + 1

  EXEC msdb.dbo.sp_send_dbmail @recipients=N'ME@somewhere.com', @body= @msg,  @query = @selectcmd ,@subject = 'SQL Server Trigger Mail', @profile_name = 'Profile Name'

END

Open in new window

0
 

Author Comment

by:sri-admin
ID: 24769234
First off, thanks for all of the feedback, I know now why I use this site!!

Many answers are close, but no cigar.  
From the top...

danrosenthal:  Maybe in there somewhere, but I'm very sorry, I should warn you I'm a bit of an amateur...  It definitely looks like it will be a cursor though, so you're on the right track for sure...

appari:  for some reason, the most basic email sent with the @query parameter doesnt seem to work for me.  But to answer your question, i would need separte emails for each record, sorry for not explaining better.  So, yes, it would need to be a Cursor I think...

ralmada:
Thanks for the tips, in the first one, it looks close, but as I forgot to mention, it would be each record being sent individually in the email.  The link looks good, it looks like this may be the path, but I'll keep it open to see if anyone else has any more specific ideas.

ONE MORE THING TO ALL...

I also forgot to mention that in this same table/View, it contains one email for each record.  So for each record, the email address it is being sent to is in the same record as the Field1, Field2, Field3 columns.  Not sure it that makes it easier or harder, but in case this helps im passing it along...

Again, thanks to everyone so far...
0
 

Author Comment

by:sri-admin
ID: 24769265
folderol:

I like it so far, where would I put the @msg declaration and where would I set that at?

acperkins:

You were right I got an error...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 10

Expert Comment

by:Banthor
ID: 24769327
So all of the above will work.
Do you want one of these guys to write the Cursor for you?
acperkins :
 I currently use this function method writing files but the fso File write method is also a function in SQL 2005
 
0
 
LVL 19

Expert Comment

by:folderol
ID: 24769367
Something like this.  

I'm assuming the email address is within the table1, so I'm changing it for every email.
declare @nextid int

declare @lastid int

declare @selectcmd nvarchar(255)

declare @sentto nvarchar(100)

DECLARE @msg varchar(500)

SET @msg = 'To confirm that you are getting an email on a particular date time "' + '.'
 

CREATE TABLE #tmploop(

	id int identity(1,1) not null,

	key1 nvarchar(100) null,

	key2 nvarchar(100) null,

         recipient nvarchar(100) null

	)

CREATE INDEX[IX_loop_id] ON [#tmploop]([id]) ON [PRIMARY]

 

insert into #tmploop(key1, key2, recipient)

select Field1, Field2, email_address_field from Table1 where Complete = N'No'

 

select @lastid = max(id) from #tmploop

set @nextid = 1

 

while @nextid <= @lastid

BEGIN

  set @selectcmd = 'select field1, field2, field3 from #tmploop join table1 on key1=field1 and key2=field2 where #tmploop.id = ' + cast(@nextid as varchar)

  select @sentto = #tmploop.recipient from #tmploop where #tmploop.id = @nextid

  set @nextid = @nextid + 1

  EXEC msdb.dbo.sp_send_dbmail @recipients=@sentto, @body= @msg,  @query = @selectcmd ,@subject = 'SQL Server Trigger Mail', @profile_name = 'Profile Name'

END

Open in new window

0
 

Author Comment

by:sri-admin
ID: 24769442
folderol:
It looks like that will probably do it, sorry, I will try to test tomorrow am and let you know first thing.

Banthor:

If they were so inclined :).
Seriously, I know im asking for quite a bit here, but I just want to have a basic setup so that I can use this for many processes that could be substantially improved.  Im just starting to explore all of the possibilities that sql offers, and im pretty amazed at this point.  

I appreciate all of the help, and if folderol's solution works, i'll award points tomorrow.
0
 

Author Comment

by:sri-admin
ID: 24769586
OK, running into a dilemma, not sure where to go with this one.  If I use the @query parameter, no matter the sql statement, it gives an error msg stating that it cannot find user "My Username".   I tried @attach_query_result_as_file = 1 with the same result.  This only happens if I use the @query parameter, so Im sorry, but apparently Im still puzzled here.  I'll continue to work with what I have so far, and if anyone has any further input Im all ears.
0
 
LVL 19

Expert Comment

by:folderol
ID: 24769613
i would need to see your code in more detail, but the query parameter does not support any passing of parameters to it or references to columns from the connection which calls it because it runs as a new session.  That's why my snippet uses the syntax CAST(@nextid as varchar).

I tested and that worked ok for me.
0
 

Author Comment

by:sri-admin
ID: 24772607
OK, narrowed it mostly down now.

Can include a query, as I wasn't including the full path to the database, so it wasnt finding the table ([database].[schema].[tablename]).  Sorry, thats my bad, never had to do that on this server before.

However, it hangs if I attempt to use the following statement, with tablenames changed of course...

set @selectcmd = 'select field1, field2, field3 from #tmploop join Alerts.dbo.table1 on key1=field1 and key2=field2 where #tmploop.id = ' + cast(@nextid as varchar)
select @sentto = #tmploop.recipient from #tmploop where #tmploop.id = @nextid


Thoughts??





set @selectcmd = 'select field1, field2, field3 from #tmploop join Alerts.dbo.table1 on key1=field1 and key2=field2 where #tmploop.id = ' + cast(@nextid as varchar)

select @sentto = #tmploop.recipient from #tmploop where #tmploop.id = @nextid

Open in new window

0
 

Author Closing Comment

by:sri-admin
ID: 31599423
The effort displayed in trying to help me, a newbie, was excellent.  Gave me all the ideas I needed to solve what should be a simple issue.  Thanks.
0
 

Author Comment

by:sri-admin
ID: 24773582
OK, the solution, it looks like, was semi-simple, but all I could figure was to use a Cursor and set it up like this:

DECLARE @Field1 nvarchar(70)
DECLARE @Field2 nvarchar(50)
DECLARE @Email nvarchar(250)
DECLARE cursorName CURSOR for
Select Field3, Field2, Email from Alerts.dbo.Table1 where Complete = N'No'
OPEN cursorName
FETCH NEXT FROM cursorName
 INTO @Field1, @Field2, @Email
 While @@FETCH_STATUS = 0
 Begin
  EXEC msdb.dbo.sp_send_dbmail
@recipients=@Email,
@body= @Field1,  
@subject = @Field2,
@profile_name = 'MY PROFILE'
  FETCH NEXT FROM cursorName
  INTO @Field1, @Field2, @Email
 End

folderol:  You spent much time and effort in helping me, and for that I'm giving you 200 points.  I think you were probably dead on, but a little advanced for what I was looking for.

danrosenthal:  You gave the best link for a Cursor example, so thank you.

Thank you to everyone, very well supported!
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 24774150
Your code looks perfect. Great work!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24782605
Banthor,
>>I currently use this function method writing files but the fso File write method is also a function in SQL 2005 <<
I have no idea what you just said.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now