DeveloperBarn

jmurrayhead · #11 May 22nd, 2008, 09:13 AM

This portion:

Code:

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday] 
(
	@birth datetime,	@email varchar(150)
)

is used to accept parameters into the procedure. If you're trying to create a variable within the procedure and set it equal to a value from a query, do this:

Code:

SET NOCOUNT ON
 
DECLARE @birth datetime
BEGIN
    SELECT @birth = (SELECT birth FROM email)
END

peebman2000 · #12 May 22nd, 2008, 09:24 AM

Okay thanks we're getting somewhere. Now I'm getting a new error:

Quote:

subquery returned more than 1 value. this is not permitted when the subquery follows =, !=, < or
when the subquery is used as an expression

Obviously because I have 3 records in the table, so i'm thinking I'll have to create a loop possibly to go through the table to check the dates.

I'm not to familiar with doing loops in sql, but i'm researching online.

But does the loop seem to be the solution for this error, or am I just way out in left field. LOL

Quote:

Originally Posted by jmurrayhead

This portion:

Code:

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday] 
(
	@birth datetime,	@email varchar(150)
)

is used to accept parameters into the procedure. If you're trying to create a variable within the procedure and set it equal to a value from a query, do this:

Code:

SET NOCOUNT ON
 
DECLARE @birth datetime
BEGIN
    SELECT @birth = (SELECT birth FROM email)
END

jmurrayhead · #13 May 22nd, 2008, 09:26 AM

Post your updated procedure

peebman2000 · #14 May 22nd, 2008, 09:29 AM

Here you go.

Code:

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

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday] 
AS 
SET NOCOUNT ON 
declare @birth datetime
BEGIN   
    --select birth, email
            --FROM email 
select @birth = (select birth 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 ='peebman@peebman.com' , --
        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'text';
	--end if
	  
  END

Quote:

Originally Posted by jmurrayhead

Post your updated procedure

jmurrayhead · #15 May 22nd, 2008, 09:55 AM

Code:

select @birth = (select birth from email)

You would need a WHERE clause for this to work so that you only get one value returned...that's why you get the error. I didn't think about that when I gave you the sample.

peebman2000 · #16 May 22nd, 2008, 10:31 AM

Here is my updated procedure, I'm still getting the subquery returned more than 1 value. error. I have 3 records in the table with dates. 1 records has 05/22/2008 and the other 2 records have 05/23/2008.

I think my where clause should work, but its not. Any ideas?

procedure:

Code:

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

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday] 
AS 
SET NOCOUNT ON 
declare @birth datetime
BEGIN   
    --select birth, email
            --FROM email 
select @birth = (select birth from email where birth = birth)
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 
		--@profile_name = 'david'
        @recipients ='peebman@peebman.com',        --@BCC = @BCCList,
        @subject = 'My Mail Test',
        @body = @body1,
        @body_format = 'text';
	--end if
	  
  END

Quote:

Originally Posted by jmurrayhead

Code:

select @birth = (select birth from email)

You would need a WHERE clause for this to work so that you only get one value returned...that's why you get the error. I didn't think about that when I gave you the sample.

jmurrayhead · #17 May 22nd, 2008, 10:47 AM

Nope, that definitely would return more than one record.

If you're trying to select all records where the birth date is equal to the current date, you'll need to do some looping. See here: How to Perform SQL Server Row-by-Row Operations Without Cursors

The above link shows methods of looping with and without using a CURSOR.

peebman2000 · #18 May 22nd, 2008, 11:28 AM

Thanks Jmurrayhead, that worked no errors, but I get no emails now. Out of the three records only 1 has today's date. Once i got one row to work, i was going to update all 3 to have the same date to test it that way. But right now it works, but not 100%, it doesn't send the email. The logic should work, but it seems like the IF @birth = getdate() is not working. I don't know.

Code:

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

ALTER PROCEDURE [dbo].[sp_proc_happyBirthday2] 
--(
	--@birth datetime,
	--@email varchar(150)
--)
AS 
SET NOCOUNT ON 
declare @birth datetime
declare row cursor for
--BEGIN   
    --select birth, email
            --FROM email 
--select @birth = (select birth from email where birth = getdate())
select birth 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 = getdate()--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

Nope, that definitely would return more than one record.

If you're trying to select all records where the birth date is equal to the current date, you'll need to do some looping. See here: How to Perform SQL Server Row-by-Row Operations Without Cursors

The above link shows methods of looping with and without using a CURSOR.

jmurrayhead · #19 May 22nd, 2008, 11:31 AM

As of today, getdate() returns 5/22/2008...however, you might have stored in your database 5/22/1976. Those dates do not match. If this is how it's setup, then you need to use some date functions to get only the month and day, not the year.

peebman2000 · #20 May 22nd, 2008, 12:08 PM

Yeah , your're right I have in the birth column 05/22/2008 12:00:00 AM. So I changed the select statment to convert it less the time to just 05/22/2008. The return results are 2008-05-22 and It's still not sending the email, the email works because it works when I just execute the dbo.sp_send_dbmail code.

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
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

As of today, getdate() returns 5/22/2008...however, you might have stored in your database 5/22/1976. Those dates do not match. If this is how it's setup, then you need to use some date functions to get only the month and day, not the year.

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

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

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

DeveloperBarn

If statement stored procedure

If statement stored procedure

LinkBack to this Thread: http://www.developerbarn.com/sql-development/206-if-statement-stored-procedure.html

Similar Threads