Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Create loop to send email in stored procedure

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
kdeutsch
Asked:
kdeutsch
  • 4
  • 3
1 Solution
 
kdeutschAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
kdeutschAuthor Commented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
exactly
0
 
kdeutschAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no. those comments are to document the BEGIN ... END parts, to know where those blocks "end", aka where they come from
0
 
kdeutschAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now