Thread: If statement stored procedure

Hey everyone its peebman2000 again, i'm having trouble with a stored procedure. I have learned and found a way to set up reminder emails using sql 2005 job scheduleing tool. The email works, but I need to write the stored procedure correctly to execute correctly.

Below is my stored procedure i'm trying to write and it's not working. Basically I'll have an email address and a deadline date column:
Table:
Deadline(Id, email, deadline)

So I'll schedule SQL to check the table everyday at noon, and to select the email and deadline date.

If the deadline date = system date then send email to the email address.

And that were i'm struggling at.

Can anyone give me any assistance on writing this type of stored procedure, i'm not too familiar with the if statement in SQL, I've done case statement but not IF statement.

Thanks for any help, I appreciate it.

My stored procedure (not working)

Code:

USE [email]
GO
/****** Object:  StoredProcedure [dbo].[proc_happyBirthday]    Script Date: 05/20/2008 16:57:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_happyBirthday] 
(
	@birth smalldatetime,
	@email varchar(150)
)
AS 
BEGIN 
    SET NOCOUNT ON 
 
   
select birth
        FROM email 
        
if @birth = getdate() then
declare @body1 varchar(100)
set @body1 = 'You belong in a zoo!'---'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail 
@recipients= 'Peebman2000@peebman.com',
--@BCC = @BCCList,
    @subject = 'My Mail Test',
    @body = @body1,
    @body_format = 'text';
	end if
END

Error Message:

Msg 156, Level 15, State 1, Procedure proc_happyBirthday, Line 52
Incorrect syntax near the keyword 'END'.

Hey jmurrayhead, thanks; but I tried your suggestion and I make the date in the birth column as 05/23/2008 12:00:00 AM and the procedure still sends the email.

Todays the 21 st so its suppose to send the email if the birth column = today's date, but it doesn't work it still sends the email no matter what.

Any other thoughts? I wander if its has to do with the msdb.dbo.sp_send_dbmail part of the email.

Code:

USE [email]
GO
/****** Object:  StoredProcedure [dbo].[proc_happyBirthday]    Script Date: 05/20/2008 16:57:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_happyBirthday] 
(
	@birth datetime,
	@email varchar(150)
)
AS 
SET NOCOUNT ON 
 
BEGIN   
    select birth
            FROM email 
END
        
IF @birth = getdate()
    BEGIN
        declare @body1 varchar(100)
        set @body1 = 'You belong in a zoo!'---'Server :'+@@servername+ ' My First Database Email '
        EXEC msdb.dbo.sp_send_dbmail 
        @recipients= 'Peebman2000@peeman.com',
        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'text';
    END

Originally Posted by jmurrayhead

Try this:

Code:

USE [email]
GO
/****** Object:  StoredProcedure [dbo].[proc_happyBirthday]    Script Date: 05/20/2008 16:57:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_happyBirthday] 
(
	@birth smalldatetime,
	@email varchar(150)
)
AS 
    SET NOCOUNT ON 
 
BEGIN   
    select birth
            FROM email 
END
        
IF @birth = getdate()
    BEGIN
        declare @body1 varchar(100)
        set @body1 = 'You belong in a zoo!'---'Server :'+@@servername+ ' My First Database Email '
        EXEC msdb.dbo.sp_send_dbmail 
        @recipients= 'Peebman2000@peebman.com',
        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'text';
    END

Hey Jmurrayhead how about something like this, like if the date does not match stop procedure, else send email. I'm trying to get it to send the email if the date matches only instead of it sending the email if the date matches or not.

Code:

IF @birth <> getdate()
	stop
else
    BEGIN
        declare @body1 varchar(100)
		
        set @body1 = 'You belong in a zoo!'---'Server :'+@@servername+ ' My First Database Email '
        EXEC msdb.dbo.sp_send_dbmail 
		--@profile_name = 'david'
        @recipients = @email, --Peebman2000@peebman.com,
        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'text';

	  
    END

Originally Posted by jmurrayhead

Try this:

Code:

USE [email]
GO
/****** Object:  StoredProcedure [dbo].[proc_happyBirthday]    Script Date: 05/20/2008 16:57:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_happyBirthday] 
(
	@birth smalldatetime,
	@email varchar(150)
)
AS 
    SET NOCOUNT ON 
 
BEGIN   
    select birth
            FROM email 
END
        
IF @birth = getdate()
    BEGIN
        declare @body1 varchar(100)
        set @body1 = 'You belong in a zoo!'---'Server :'+@@servername+ ' My First Database Email '
        EXEC msdb.dbo.sp_send_dbmail 
        @recipients= 'Peebman2000@peebman.com',
        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'text';
    END

This has to work, i'm researching online, do you know how to stop a stored procedure within a stored procedure?

Originally Posted by jmurrayhead

I'm not very familiar some of these features, but maybe something like this then:

Code:

IF @birth <> getdate()
    BEGIN    
        Return 0 
    END
ELSE
    BEGIN
        declare @body1 varchar(100)
 
        set @body1 = 'You belong in a zoo!'---'Server :'+@@servername+ ' My First Database Email '
        EXEC msdb.dbo.sp_send_dbmail 
        --@profile_name = 'david'
        @recipients = @email, --Peebman2000@peebman.com,
        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'text';
 
 
    END

Hey jmurrayhead thanks, I found out the reason why it kept sending the emails with out checking the birth(date column). I had the store procedure excecuting off the wrong database, I have another database with a practice table for sending emails.

But now I have it on the right database and I'm using this stored procedure:

Code:

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

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday] 
(
	@birth datetime,
	@email varchar(150)
)
AS 
SET NOCOUNT ON 
 
BEGIN   
    select birth, email
            FROM email 
END

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

And I get this error when I schedule the job to run this stored procedure:

Procedure or function sp_proc_happybirthday expectes parameter @birth, which was not supplied
the step failed

Should I like set @birth = birth, I don't know any ideas?

Originally Posted by jmurrayhead

Not sure what you mean, but the IF statement should prevent the procedure from even being called.

Thanks, I don't know I don't understand. If I use the simple select statement that used in the stored procedure:

Code:

select birth, email
            FROM email

It brings back this, and I have three records in the table.
Column(which is the date column)
birth(05/22/2008 12:00:00 AM, 05/23/2008 12:00:00 AM, 05/23/2008 12:00:00 AM)

email(peebman2000@peeb.com, peeb@peeb.com, peebman@peebman.com)

So the dates are in the birth column, I don't understand why the date from the birth column is not supplied to the stored procedure.

Code:

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

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday] 
(
	@birth datetime,	@email varchar(150)
)
AS 
SET NOCOUNT ON 
 
BEGIN   
    select birth, email
            FROM email 
END

IF @birth = getdate()--getdate()
	
	Begin
        declare @body1 varchar(100)
		
        set @body1 = 'You belong in a zoo!'---'Server :'+@@servername+         EXEC msdb.dbo.sp_send_dbmail 
		        @recipients = @email,        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'text';
	--end if
	  
  END

Originally Posted by jmurrayhead

To set variables, you do this:

Code:

SET @variable = 'whatever'

However, it appears that the parameter @birth isn't being supplied to the stored procedure.