Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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