asked on

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.

ASKER CERTIFIED SOLUTION

danrosenthal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

appari

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'

ralmada

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

appari

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.

ralmada

if it's the way appari says, you can also have a look at this:
https://www.experts-exchange.com/questions/23800989/populating-recipients-value-using-msdb-dbo-sp-send-dbmail-from-a-database-table.html?sfQueryTermInfo=1+each+email+member+send+sql+tabl

Banthor

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

Anthony Perkins

>> 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."

SOLUTION

folderol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sri-admin

ASKER

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...

sri-admin

ASKER

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...

Banthor

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

folderol

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

sri-admin

ASKER

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.

sri-admin

ASKER

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.

folderol

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.

sri-admin

ASKER

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

sri-admin

ASKER

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.

sri-admin

ASKER

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!

danrosenthal

Your code looks perfect. Great work!

Anthony Perkins

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.