Solved

Create loop to send email in stored procedure

Posted on 2010-09-20
7
257 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
exactly
0
 

Author Comment

by:kdeutsch
Comment Utility
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]
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now