[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Need help with stored procedure ASAP!!

I have a stored procedure that sends out emails to all the customers.  I need to add to the procedure now to get the count of emails sent and automatically send an email to different departments the count.  Can somebody help me with this.  Greatly appreciate.
Thanks,
Lalitha
0
Lalitha_Ramalingam
Asked:
Lalitha_Ramalingam
  • 4
  • 2
  • 2
1 Solution
 
Kevin3NFCommented:
without seeing your proc, all I can suggest is that you add a counter variable and increment it each time through your loop (if you are looping), or set it equal to the number of records you SELECT to send emails to.

Please post the proc....
0
 
Lalitha_RamalingamAuthor Commented:
CREATE PROCEDURE [dbo].[ReminderEmail3 ]
 
AS
 
DECLARE @PNR_No varchar(6)
DECLARE @Email_Add varchar(50)
DECLARE @EmailLine varchar(1000)
DECLARE @recipients varchar(200)
DECLARE @subject varchar(1000)
DECLARE @cmd varchar(200)
DECLARE @From as varchar(55)
DECLARE @Body varchar(8000)
 
SET @Body = ' '
DECLARE email_cursor CURSOR FOR
select distinct  Record_Locator, Email_address  from dbo.Info
OPEN email_cursor
 
FETCH NEXT FROM email_cursor
INTO @PNR_No, @Email_Add
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
 
      SET @cmd='exec Reports..ReminderEmail_sp @pnr_no = "'+ @PNR_no + '" , @email_add = "' +ltrim(@Email_add) + '"'
      exec (@cmd)
 
      SET @Recipients=@Email_add
                      
       
       DECLARE Email_Cur CURSOR FOR select fld2 from tmp_email
 
      OPEN Email_Cur
 
      SET @Body = ' '
      FETCH NEXT FROM Email_cur INTO @EmailLine
      WHILE @@FETCH_STATUS = 0
      BEGIN
     
             SET @Body = @Body + ltrim(@EmailLine)+'<br>'

            FETCH NEXT  FROM Email_cur INTO @EmailLine
      END
             
      exec master..sp_send_cdosysmail  From, @Recipients, 'Important information',  @Body
       CLOSE Email_cur
       DEALLOCATE Email_cur

       FETCH NEXT FROM email_cursor  INTO @PNR_No, @Email_add
 
END

 CLOSE  email_cursor
DEALLOCATE email_cursor
GO
0
 
Lalitha_RamalingamAuthor Commented:
Thanks for your reponse.  I have posted my procedure.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Kevin3NFCommented:
Seems like you could stick a counter variable inside the cursor pretty easily.  I am not that familiar with them, so I don't want to suggest where..  After the loop you add another send mail process including the counter variable's value
0
 
Scott PletcherSenior DBACommented:
Code below should give you the total; I marked lines added with "--++":


DECLARE @PNR_No varchar(6)
DECLARE @Email_Add varchar(50)
DECLARE @EmailLine varchar(1000)
DECLARE @recipients varchar(200)
DECLARE @subject varchar(1000)
DECLARE @cmd varchar(200)
DECLARE @From as varchar(55)
DECLARE @Body varchar(8000)
DECLARE @EmailCount INT  --++

SET @EmailCount = 0  --++
 
SET @Body = ' '
DECLARE email_cursor CURSOR FOR
select distinct  Record_Locator, Email_address  from dbo.Info
OPEN email_cursor
 
FETCH NEXT FROM email_cursor
INTO @PNR_No, @Email_Add
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
 
     SET @cmd='exec Reports..ReminderEmail_sp @pnr_no = "'+ @PNR_no + '" , @email_add = "' +ltrim(@Email_add) + '"'
     exec (@cmd)
 
     SET @Recipients=@Email_add
                     
       
      DECLARE Email_Cur CURSOR FOR select fld2 from tmp_email
 
     OPEN Email_Cur
 
     SET @Body = ' '
     FETCH NEXT FROM Email_cur INTO @EmailLine
     WHILE @@FETCH_STATUS = 0
     BEGIN
     
           SET @Body = @Body + ltrim(@EmailLine)+'<br>'

          FETCH NEXT  FROM Email_cur INTO @EmailLine
     END
             
     exec master..sp_send_cdosysmail  From, @Recipients, 'Important information',  @Body
      SET @EmailCount = @EmailCount + 1  --++
      CLOSE Email_cur
      DEALLOCATE Email_cur

      FETCH NEXT FROM email_cursor  INTO @PNR_No, @Email_add
 
END

CLOSE  email_cursor
DEALLOCATE email_cursor

SET @body = 'Total number of emails sent was ' + CAST(@EmailCount AS VARCHAR(10)) --++
--++ set recipients, etc.
exec master..sp_send_cdosysmail  ... --++
--
GO
0
 
Lalitha_RamalingamAuthor Commented:
Thank you.  I worked it out.
0
 
Scott PletcherSenior DBACommented:
Oops, sorry, I was just trying to make sure it got answered :-) .
0
 
Lalitha_RamalingamAuthor Commented:
Thanks for the help.
0

Featured Post

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.

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