Any reason why this stored proc isn't inserting records?

Hi Experts,

This stored proc is supposed to select records from a table called tblLogin and inserts the records into Notifictions table.

Finally, grabs the records and emails the contents using sp_send_dbmail.

When I execute this stored proc, it executes successfully but no record is being saved on the Notifications table.

Any ideas what I am doing wrong?

Thanks lot in advance.

Code is below.

ALTER PROCEDURE [dbo].[GetRegistrationInfo] 
 AS

 BEGIN
 DECLARE Register_Cursor CURSOR FOR 

 SELECT LoginId,
  FullName,
   email, 
   Password
 FROM  dbo.tblLogin
 WHERE ModifiedDate = getdate()
 ORDER BY LoginId DESC

 OPEN Register_Cursor
 Declare @LoginId int
 Declare @fullname nvarchar(100)
 Declare @email nvarchar(MAX)
 Declare @password nvarchar(20)
 -- Get the current MAX ID 
 Declare @mailID as int  
  -- Start reading each record from the cursor. 
 FETCH Register_Cursor into @LoginId,@fullname,@email,@password
 WHILE @@FETCH_STATUS = 0 
  BEGIN 
  --set @mailID = (SELECT max(mailID) from PledgeNotification) Not needed; let's auto-genereate the id
  INSERT into dbo.PledgeNotification (mailContent,
                                 LoginId,
                                 FullName,
                                 email,
                                 Password,
                                 sender,
                                 Sent) 
 VALUES ( 
'This is a computer generated email message. 
Please DO NOT use the REPLY button above to respond to this email. 

Dear '+@FullName+': 

Thanks for registering to take Training!

Below are details of your registration information:

Your UserName is: '+@email+'. 
 
Your Password is: '+@password+'. 

Once you have retrieved your login information, please click the link below to get back to the Training login screen and begin to begin to enjoy the benefits of membership. 


http://servername/training/

Regards, 
The Office.', 
 @LoginId,
 @FullName,
 @email,
 @Password,
  'NoReply@domain.com',
'No'
 ) 
  
 FETCH Register_Cursor into @LoginId,@FullName,@email,@password
 END
  
 CLOSE Register_Cursor  
 DEALLOCATE Register_Cursor
 END
   
 BEGIN
 DECLARE MAIL_CURSOR CURSOR FOR
  
 select mailid, sender, mailcontent
 from Notifications
 where Sent = 'No'
   
 Declare @mail1 int 
 Declare @sender nvarchar(100)
 declare @content1 nvarchar(4000) 
  
 OPEN MAIL_CURSOR
  
 FETCH MAIL_CURSOR into @mail1, @sender,@content1
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
 
		SELECT @email = @email+';'+Email
		FROM Notifications
    WHERE sent = 'No'
 
--exec sp_send__dbmail @mail1, null,null,@content1,null

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'The Registrations & Elections Office', 
@recipients = @email, -- your email
--@blind_copy_recipients = @email,
@subject = 'Your Account Details',
@body = @content1;

--Update the record in Notifications table where Sent = 'No'.

Update Notifications SET Sent = 'Yes' WHERE Sent = 'No' and mailid = @mail1
 
 FETCH MAIL_CURSOR INTO @mail1, @sender, @content1
 END
  
 CLOSE MAIL_CURSOR
 DEALLOCATE MAIL_CURSOR
 END

exec [GetRegistrationInfo]

Open in new window

LVL 29
sammySeltzerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris LuttrellSenior Database ArchitectCommented:
The first thing I notice is that your query in your cursor is
SELECT LoginId,
  FullName,
   email, 
   Password
 FROM  dbo.tblLogin
 WHERE ModifiedDate = getdate()
 ORDER BY LoginId DESC

Open in new window

with the WHERE CLAUSE looking for the exact datetime of right now which will probably never find any records.  Don't you mean to be looking for records since some point in the past?
0
sammySeltzerAuthor Commented:
Thank you for the prompt response.

I was looking for  a way to capture anyone who just registered.

In other words, if you sign up for an account, and click submit to tblLogin, I want to grab that information and store it in Notifications table.

The only thing I can think to do is use ModifiedDate which is the date and time the user submits his/her registration.

Do you have any ideas how to do it differently from what i have?
0
Chris LuttrellSenior Database ArchitectCommented:
Can you change your tblLogin to have a field like ProcessedFlag (with a default value of 'No') and modify your cursor to use it like this?
 DECLARE Register_Cursor CURSOR FOR 

 SELECT LoginId,
  FullName,
   email, 
   Password
 FROM  dbo.tblLogin
 WHERE ProcessedFlag = 'No'
 ORDER BY LoginId DESC

Open in new window

and then at the end of your cursor loop along with the existing Update include a new line like this:
Update Notifications SET Sent = 'Yes' WHERE Sent = 'No' and mailid = @mail1
Update tblLogin SET ProcessedFlag = 'Yes' WHERE ProcessedFlag = 'No' and LoginId = @LoginId

Open in new window

With this you pick up any new unprocessed records, loop through them once and set them to processed when done.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sammySeltzerAuthor Commented:
THat seems to work for me.

Thanks alot
0
Chris LuttrellSenior Database ArchitectCommented:
You're very welcome!  Glad to have helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.