Solved

Create loop to send email in stored procedure

Posted on 2010-09-20
7
263 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 142

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

831 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