Solved

Create loop to send email in stored procedure

Posted on 2010-09-20
7
264 Views
Last Modified: 2012-05-10
I am just checking to see if there are better methods than doing cursors in a stored procedure.  In the past i was told don't use them.  So in the code below I am gettng records that I need to send out emails on. So first I go out and grab all the records, then I need to grab the emails for the records in the next statment.  Then I need to email out all the information.  I can take care of the email protion no problem but without the use of a curos how can i loop through the recordset then loop trhough find email , then send out email and loop agian.
Create Proc dbo.ACNExpire

AS

Declare		@ACN as Varchar(10),@DtExpire as dateTime,@RecruitName as varchar(60),@RSID as integer,
			@NCOIDSid as Integer, @RecruiterEmail as varchar(100), @NCOEmail as varchar(100)
						
							
				Select * from OPENQUERY(SIDPERS, 'Select ACN, RSID, NCOID, to_char(DT_EXPIRE, ''YYYY/MM/DD'') DT_EXPIRE, 
												APP_NAME from ACN.ACN_CNTL_NBR_TBL
												where (decode(to_char(sysdate,''D''),4,next_day(sysdate,''MON'')
												,5,next_day(sysdate,''TUE''),6,next_day(sysdate,''TUE''),7,
												next_day(sysdate,''TUE''),sysdate+2)) > 
												DT_EXPIRE and sysdate < DT_EXPIRE') 
												
				Select strEmail from WorsDotNet.dbo.tblUser where intID = 
				(Select intRecruiterId from RecruitDotNet.dbo.tblRecruiter where intRecruiterRecordId = 98)

Open in new window

0
Comment
Question by:kdeutsch
  • 4
  • 3
7 Comments
 

Author Comment

by:kdeutsch
ID: 33719783
So first query returns this record set.
ACn              RSID           NCOIC                DT_EXPIRE       name
10-257-003      8      98      2010/09/22      Doe, John
10-257-004      8      98      2010/09/22      Doe, mary
10-260-001      207      50      2010/09/21      Doe, henry

The next query return a specific email for each record, which then I need to send out an email and repeat till all emails are snet out.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33719966
you want something like this:
Create Proc dbo.ACNExpire
AS
declare @t table ( ACN as Varchar(10), RSID as integer
                 , NCOIDSid as Integer 
		 , DtExpire as dateTime                  
                 )
Declare		@ACN as Varchar(10),@DtExpire as dateTime,@RecruitName as varchar(60),@RSID as integer,
			@NCOIDSid as Integer, @RecruiterEmail as varchar(100), @NCOEmail as varchar(100)
						
INSERT INTO @t  
  Select * 
    from OPENQUERY(SIDPERS, 'Select ACN, RSID, NCOID, to_char(DT_EXPIRE, ''YYYY/MM/DD'') DT_EXPIRE
               ,  APP_NAME from ACN.ACN_CNTL_NBR_TBL
	where (decode(to_char(sysdate,''D''),4,next_day(sysdate,''MON'')
 		,5,next_day(sysdate,''TUE''),6,next_day(sysdate,''TUE'')
                ,7,next_day(sysdate,''TUE''),sysdate+2)) >  DT_EXPIRE 
           and sysdate < DT_EXPIRE') 
												
WHILE @@rowcount <>0
BEGIN
  SELECT TOP 1 @ACN = ACN
        , @DtExpire = DtExpire
        , @RSID = RSID
        , @NCOIDSid 
   FROM @t


  IF @@rowcount <> 0
  BEGIN
       Select @@RecruiterEmail = strEmail 
          from WorsDotNet.dbo.tblUser where intID = 
	 (Select intRecruiterId from RecruitDotNet.dbo.tblRecruiter where intRecruiterRecordId = @RSID )

    --- do send the email here ...


  END -- end if @@rowcount <> 0
 
  DELETE @t
   WHERE ncoidsid = @ncoidsid 
     AND acn = @acn
     AND RSID = @RSID

END -- WHILE @@rowcount <> 0

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 33720137
angelIII,
Ok will try this out an let you know, so If i understand what is going on here, you are inserting the first statement into a temp table and then selecting from that table until you can select no more records.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33720143
exactly
0
 

Author Comment

by:kdeutsch
ID: 33720267
angelIII:,

Ok, looks like it is working, I have to go back and find email struff I did on an earlier stored procedure.  When adding in the emailing I take it I uncomment  the follwing lines and use the end if comments.

 END -- end if @@rowcount <> 0
 
  DELETE @t
   WHERE ncoidsid = @ncoidsid
     AND acn = @acn
     AND RSID = @RSID

END -- WHILE @@rowcount <> 0
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33720277
no. those comments are to document the BEGIN ... END parts, to know where those blocks "end", aka where they come from
0
 

Author Closing Comment

by:kdeutsch
ID: 33720394
Thanks for the quick help, do you have some where on web where I can reference and learn to do this??  I just need to add a new email profile to sql mail and it should work just fine.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

789 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