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

peebman2000 · #1 May 21st, 2008, 12:10 PM

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:

Quote:

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

jmurrayhead · #2 May 21st, 2008, 01:11 PM

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

peebman2000 · #3 May 21st, 2008, 02:29 PM

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

Quote:

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

peebman2000 · #4 May 21st, 2008, 02:50 PM

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

Quote:

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

jmurrayhead · #5 May 21st, 2008, 02:57 PM

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

peebman2000 · #6 May 21st, 2008, 03:26 PM

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

Quote:

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

jmurrayhead · #7 May 22nd, 2008, 08:39 AM

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

peebman2000 · #8 May 22nd, 2008, 08:55 AM

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:

Quote:

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?

Quote:

Originally Posted by jmurrayhead

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

jmurrayhead · #9 May 22nd, 2008, 08:59 AM

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.

peebman2000 · #10 May 22nd, 2008, 09:09 AM

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

Quote:

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.

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.