Thread: If statement stored procedure

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

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

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

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

Originally Posted by jmurrayhead

Post your updated procedure

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

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.

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

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.

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

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.