?
Solved

Create loop to send email in stored procedure

Posted on 2010-09-20
7
Medium Priority
?
267 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 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