SQL Stored Procedure Loop for Job

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.
sri-adminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

danrosenthalCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
appariCommented:
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
ralmadaCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

appariCommented:
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
BanthorCommented:
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
Anthony PerkinsCommented:
>> 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
folderolCommented:
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
sri-adminAuthor Commented:
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
sri-adminAuthor Commented:
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
BanthorCommented:
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
folderolCommented:
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
sri-adminAuthor Commented:
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
sri-adminAuthor Commented:
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
folderolCommented:
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
sri-adminAuthor Commented:
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
sri-adminAuthor Commented:
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
sri-adminAuthor Commented:
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
danrosenthalCommented:
Your code looks perfect. Great work!
0
Anthony PerkinsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.