Thread: If statement stored procedure

I'm sorry dude I may have confused you during all these post, but I want it to send an email to someone who was born today. I'm testing the email part,because in the app i'm building the user will enter in a date they want a reminder email sent. So i'll schedule sql to run this stored procedure on the column that has the reminder email date. So if the reminder email date = today's date, shoot an email. That way the reminder email is sent on the date the user wants the reminder email.

What I have should work the way I want it.

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
declare row cursor for
--select @birth = (select birth from email where birth = birth))
select convert(varchar(10), birth, 120) from email --where birth = birth

open row

fetch row into @birth

while @@fetch_status = 0

begin
	fetch row into @birth
end
close row

Deallocate row

return

--END


 --BEGIN       
IF @birth = convert(varchar(10), getdate(), 120)--getdate()
	
	Begin
        declare @body1 varchar(100)
		
        set @body1 = 'You belong in a zoo!'---'Server :'+@@servername+         EXEC msdb.dbo.sp_send_dbmail 
		--@profile_name = 'david'
        @recipients ='peebman@peebman.com', --
        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'text';
	end

Originally Posted by jmurrayhead

I don't see how that could ever work. Basically, you're telling it to send the email to someone who was born today...not 27 years ago on this date. You need to search the database to return records that have a birthdate of 5/22/any year, not 5/22/2008. For this, you can use a Date function: DATEPART (T-SQL)

Dude i'm about to just scratch it, its not working the way I need. The logic and everything should work, but its not. Here what i have, it sends the email now, but again sending it if the date matches getdate or not.

I had a date of 05252008 and it still sent the email, I also had today' date of 05222008 and it still sent the email.

I thought it might be something with the msdb.dbo.sp_send_dbmail function, that it defaults to sending the email no matter what the condition is, but everything I researched shows there no default.

procdure:

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
declare row cursor for
select convert(varchar(8), birth, 112) from email where birth = convert(varchar(8), getdate(), 112)

open row

fetch row into @birth

while @@fetch_status = 0

begin
	fetch row into @birth
IF convert(varchar(8), @birth, 112) = convert(varchar(8), getdate(), 112)-
	Begin
        declare @body1 varchar(100)
		
        set @body1 = 'You belong in a zoo!'   
      EXEC msdb.dbo.sp_send_dbmail 
		
        @recipients ='peebman@peebman.com', --
        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'html';
	end 
else if @birth <> convert(varchar(8), getdate(), 112)
BEGIN
	EXEC msdb.dbo.sysmail_stop_sp	
	End
end
close row

Deallocate row

return

Originally Posted by jmurrayhead

Instead of all of that...why aren't you just using the query to select all records that equal today's date, (i.e. SELECT * FROM email WHERE birth = getdate())? Then manipulate those records...that would make the most sense to me.

Well the mother heffer works, I feel like doing some jumping jacks or something.

I entered in today's date into the birth column, again I have 3 records. It shot me an email, 3 emails exactly, I guess because there were 3 records that matched the condition.

Then I changed the date to 05252008 and it shot me NO emails.

Okay so its working now how I want it, and thanks Jmurraryhead.

But I got one more issue. The email addresses in the email column. The current 3 records I have have 3 different email address. How can I include the @email to that if it matches the condition it sends the email to the 3 different email address?

Code:

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

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday2] 
AS 
SET NOCOUNT ON 
declare @birth datetime
declare row cursor for
select convert(varchar(8), birth, 112) from email where birth = convert(varchar(8), getdate(), 112)

open row

fetch row into @birth

while @@fetch_status = 0

begin
	fetch row into @birth
IF convert(varchar(8), @birth, 112) = convert(varchar(8), getdate(), 112)
	Begin
        declare @body1 varchar(100)
		
        set @body1 = 'You belong in a zoo!'
        EXEC msdb.dbo.sp_send_dbmail 
		--@profile_name = 'david'
        @recipients = @email, --        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'html';
	end 
else if convert(varchar(8), @birth, 112) <> convert(varchar(8), getdate(), 112)
BEGIN
	EXEC msdb.dbo.sysmail_stop_sp	
	End
end
close row

Deallocate row

return

Originally Posted by jmurrayhead

Using Query Analyzer, take this query:

Code:

select convert(varchar(8), birth, 112) from email where birth = convert(varchar(8), getdate(), 112)

and see what records it returns.

Okay jmurrayhead, here's what I got for Part B. but it gives me an error.

Here's the 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 
declare @birth datetime, @email varchar(150)

declare row cursor for
select email, convert(varchar(8), birth, 112) from email where birth = convert(varchar(8), getdate(), 112)

open row

fetch next from row into @birth, @email

while @@fetch_status = 0

begin
	fetch next from row into @birth, @email
IF convert(varchar(8), @birth, 112) = convert(varchar(8), getdate(), 112)
	Begin
        declare @body1 varchar(100)
		
        set @body1 = 'You belong in a zoo!'
        EXEC msdb.dbo.sp_send_dbmail 
		
        @recipients = @email,
        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'html';
	end 
else if convert(varchar(8), @birth, 112) <> convert(varchar(8), getdate(), 112)
BEGIN
	EXEC msdb.dbo.sysmail_stop_sp	
	End
end
close row

Deallocate row

return

And here's the error:

conversion failed when converting datetime from character to string

Now I shouldn't get this error because the convert should work.

....Or should I use a Cast for the date, but I thought Cast and Convert are basically the same thing. Any ideas on the error?

Originally Posted by jmurrayhead

Glad it's working for you...see Part B here FETCH (T-SQL)