Discuss "If statement stored procedure" in the SQL Development forum.

jmurrayhead · #21 May 22nd, 2008, 12:27 PM

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)

peebman2000 · #22 May 22nd, 2008, 12:46 PM

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

Quote:

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)

jmurrayhead · #23 May 22nd, 2008, 01:03 PM

Try this:

Code:

IF CONVERT(varchar(8), @birth, 112) = CONVERT(varchar(8), getdate(), 112)

and wouldn't it make sense to place the email portion within your loop?

jmurrayhead · #24 May 22nd, 2008, 01:08 PM

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.

peebman2000 · #25 May 22nd, 2008, 02:31 PM

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

Quote:

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.

jmurrayhead · #26 May 22nd, 2008, 02:33 PM

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.

peebman2000 · #27 May 22nd, 2008, 02:58 PM

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

Quote:

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.

jmurrayhead · #28 May 22nd, 2008, 03:02 PM

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

peebman2000 · #29 May 22nd, 2008, 03:57 PM

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:

Quote:

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?

Quote:

Originally Posted by jmurrayhead

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

jmurrayhead · #30 May 23rd, 2008, 08:49 AM

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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Dynamic Stored Procedure	jmurrayhead	Microsoft SQL Server	16	March 26th, 2008 11:19 AM
Permissions on Tables, Stored Procedures, etc.	theChris	Microsoft SQL Server	2	March 24th, 2008 11:49 AM

LinkBacks (?) LinkBack to this Thread: http://www.developerbarn.com/sql-development/206-if-statement-stored-procedure.html
Posted By	For	Type	Date
how to create a stored procedure to send mail xp_send_mail...? - ASP.NET Forums	This thread	Refback	December 23rd, 2008 08:19 AM
how to create a stored procedure to send mail xp_send_mail...? - ASP.NET Forums	This thread	Refback	December 17th, 2008 05:14 AM
how to create a stored procedure to send mail xp_send_mail...? - ASP.NET Forums	This thread	Refback	December 16th, 2008 06:53 AM

Discuss "If statement stored procedure" in the SQL Development forum.

SQL Development - Structured Query Language (SQL) is the talk of databases. Discuss topics such as joins, triggers and other advanced SQL topics.