Thread: If statement stored procedure

Yeah, I tried it without the conversion, (see below) and I get no error's, but the email does go NOW. I changed the dates in the 3 records to match today's date and it should have sent the email like it did before. But of course I now selecting the email address and date now.

But I"m going to put the conversion part back in and try it again. I'm looking on line to see if there is something about that datetime conversion error.

Any other ideas let me know.

Code:

USE [peebman]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday2] 
AS 
SET NOCOUNT ON 
declare @birth datetime, @email varchar(150)

declare row cursor for
select email, birth from email where birth = getdate()

open row

fetch row into @birth, @email

while @@fetch_status = 0

begin
	fetch row into @birth, @email
IF  @birth = getdate()
	
	Begin
        declare @body1 varchar(100)
		
        set @body1 = 'You are reminded to finish your PDQ!'---'Server         EXEC msdb.dbo.sp_send_dbmail 
		
        @recipients = @email,     
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'html';
	end 
else if @birth <> getdate()
BEGIN
	EXEC msdb.dbo.sysmail_stop_sp	
	End
end
close row

Deallocate row

return

Originally Posted by jmurrayhead

Not sure what's causing the error all of a sudden. Have you tried it again without the conversion to see if that is actually the issue?

Okay Jmurrayhead, I finally got everything to work. I appreciate the links you sent me they help as well, but it works now. I can schedule for reminder emails to be sent out using sql 2005 job scheduler.

Here is my final stored procedure:

Code:

USE [peebman]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday2] 
AS 
SET NOCOUNT ON 
exec msdb.dbo.sysmail_start_sp
declare @birth varchar(15),
@email varchar(8000)
set @email =''
declare row cursor for
select email, birth from email where birth = CONVERT(varchar(15), GETDATE(), 101)
-- = convert(varchar(8), getdate(), 112)

open row

fetch row into @email, @birth

while @@fetch_status = 0

begin
	fetch row into @email, @birth
IF @birth = CONVERT(varchar(15), GETDATE(), 101)
	
	Begin
        declare @body1 varchar(100)
		
        set @body1 = 'You are reminded to finish your PDQ!'---'Server 
        EXEC msdb.dbo.sp_send_dbmail 
		
        @recipients = @email,        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'html';
	end 
else if  @birth <> CONVERT(varchar(15), GETDATE(), 101)
BEGIN
	EXEC msdb.dbo.sysmail_stop_sp	
	End
end
close row

Deallocate row

return

I added some more msdb.dbo.sysmail code and I changed the birth(datecolum) to a varchar to get rid of the "Conversion datetime to character string" error.

And I converted getdate to a varchar to match the birth(datecolumn) in the table.

That worked as well as the msdb.dbo.sysmail_start_sp code to shoot the email.

also before in the begin of loop I had:

Code:

fetch row into @birth, @email

well the select statment selects email, birth not birth, email so I changed it to:

Code:

fetch row into @email, @birth

and i think that helped as well.

So thinks again for the help, sorry for all the postings. Hopfully this post will help other programmers out who want to sent reminder emails using SQL 2005 instead of using executables on the server.

Thanks again