October 5, 2012 at 10:17 am
I am setting up a Proc with
if (Select birth_date from juvenile Where @birth_date >= GETDATE() - (18*365.25)) is null
begin
raiserror('Not a Juvenile',11, 1)
end
if the birthdate is the age of 18 or older it will show not a Juvenile if the age is less than 18 it will insert the information into the table.
October 5, 2012 at 10:41 am
Was there a problem? It seems like this would work, even if it's a bit less intuitive than using a datediff.
October 5, 2012 at 11:04 am
i get this error
Msg 50000, Level 11, State 1, Procedure AddJuvenileMember, Line 98
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 266, Level 16, State 2, Procedure AddJuvenileMember, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
October 5, 2012 at 11:08 am
I think you need to move your last close parens to after NULL.
EDIT: No, that's not it. That code by itself executes fine, using a sample table I created. When you run just the select portion of your conditional statement, how many rows are returned? There can only be one row evaluation.
October 5, 2012 at 11:19 am
(1 row(s) affected)
here is the Proc
use master
go
set ansi_nulls on
go
set quoted_identifier on
go
-------------------------------------------
------Author: <Eddie Hall>
------CreatedDate <10/04/2012>
------Description: <Create Juvenile, insert into juvenile, First Name>
-------------------------------------------
alter Proc Dbo.AddJuvenileMember
@lastname varchar(15) = null,
@firstName varchar(15) = null,
@middleInitial char(1) = null,
--------@street varchar (15) = null,
--------@city varchar (165) = null,
--------@state char(2) = null,
--------@zip varchar(10) = null,
--------@phone_no char(13) = null,
@adult_member_no smallint = null,
@birth_date datetime = null
as
begin
set nocount on;
begin try
begin transaction;
if @lastname is null or @lastname = '' or @lastname = ' '
begin
raiserror('Need Last Name',11, 1)
end
if @firstname is null or @firstname = '' or @firstname = ' '
begin
raiserror ('Need First Name',11, 1)
end
--------if @street is null or @street = '' or @street = ' '
--------begin
--------raiserror('Need Street Address',11, 1)
--------end
--------if @city is null or @city = '' or @city = ' '
--------begin
--------raiserror('Need City',11, 1)
--------end
--------if @zip is null or @zip = '' or @zip = ' '
--------begin
--------raiserror('Need Zip Code',11, 1)
--------end
--------------------is this on the right track to check to see if adult is in the adult table
IF(SELECT member_no from member where member_no = @adult_member_no) is null
BEGIN
RAISERROR('Customer doesn''t exist', 11, 1)
END
if (Select birth_date from juvenile Where @birth_date >= GETDATE() - (18*365.25)) is null
begin
raiserror('Not a Juvenile',11, 1)
end
----begin insert into members table----
insert into dbo.member
(
lastname,
firstname,
middleinitial
)
values
(
@lastname,
@firstName,
@middleInitial
)
----insert into Juvenile table----
Declare @Member_no int
Set @Member_no = SCOPE_IDENTITY()
insert into juvenile
(
member_no,
adult_member_no,
birth_date
)
values
(
@Member_no,
@adult_member_no,
@birth_date
)
commit Transaction;
end Try
begin Catch
if @@trancount > 0
Declare @errorMessage nvarchar(4000),
@errorstate int
Select @errorMessage = ERROR_MESSAGE(),
@errorstate = ERROR_STATE()
raiserror (@errorMessage, 11, @errorState)
Return
End Catch
end
October 5, 2012 at 11:25 am
your error is in this validation line:
IF(SELECT member_no from member where member_no = @adult_member_no) is null
BEGIN
RAISERROR('Customer doesn''t exist', 11, 1)
END
if there are TWO or more rows in the table member that ahs the same member_no, you get that error;
here's a proof of concept:
IF(SELECT
member_no
FROM (SELECT 1 member_no UNION ALL SELECT 1 UNION ALL SELECT 2) xAS
WHERE member_no = 1) IS NULL
BEGIN
RAISERROR('Customer doesn''t exist',
11,
1)
END
instead, you want to use IF NOT EXISTS to validate instead, that lets you test multi rows/no rows like you are trying to do here:
IF NOT EXISTS(SELECT
member_no
FROM (SELECT 1 member_no UNION ALL SELECT 1 UNION ALL SELECT 2) xAS
WHERE member_no = 1)
BEGIN
RAISERROR('Customer doesn''t exist',
11,
1)
END
Lowell
October 5, 2012 at 11:33 am
Ok, I don't understand this line, given the context of the sproc:
Select birth_date from juvenile Where @birth_date >= GETDATE() - (18*365.25)
This just doesn't make sense. You're selecting from the juvenile table using a where clause that doesn't evaluate any of the fields in the juvenile table. If the @birth_date evaluates to older than 18, this query will return every row in the table. This is both what's wrong with your logic and what's wrong with SQL's handling of it. SQL only wants to evaluate one result in this statement, but you're forcing it to try to evaluate many.
Why don't you just do this instead?
IF @birth_date >= GETDATE() - (18*365.25)
BEGIN
RAISERROR('blah')
END
October 5, 2012 at 11:35 am
uh, yeah...what Lowell said, too. both are issues.
October 5, 2012 at 11:37 am
still getting error
Msg 50000, Level 11, State 1, Procedure AddJuvenileMember, Line 103
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 266, Level 16, State 2, Procedure AddJuvenileMember, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
(1 row(s) affected)
October 5, 2012 at 11:37 am
I think the selection criteria below will give you a much more accurate result, especially taking into account birthdays like Feb 29.
select
birth_date
into
#juvenile
from
( --Test Data
select birth_date = convert(date,'1994-10-03')union all
select birth_date = convert(date,'1994-10-04')union all
select birth_date = convert(date,'1994-10-05')union all
select birth_date = convert(date,'1994-10-06')
) a
select
birth_date,
CurrDateTime= getdate()
from
#juvenile
where
birth_date >= dateadd(yy,-18,convert(date,getdate()))
Results:
birth_date CurrDateTime
---------- -----------------------
1994-10-05 2012-10-05 13:32:29.307
1994-10-06 2012-10-05 13:32:29.307
October 5, 2012 at 11:42 am
Again, this error means that you have a conditional evaluation (<, >, =, <>, etc.) where SQL is expecting one value to evaluate against, but you are supplying many. Look at any case where you are doing this:
if select [fieldname] from
< [value]
begin
...
end
October 5, 2012 at 11:50 am
ok thanks I will try that
October 5, 2012 at 12:39 pm
It lets me insert into the tables but if I enter birthday of 1955-10-17 it should give me an error saying this is not a juvenile and not insert into the tables.
Figured it out I had it >= getdate instead of <=getdate
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply