June 15, 2007 at 1:02 pm
If
(Select Deleteemp FROM OPENXML (@empIDdoc, '/emps/Item', 1)WITH(ChangeToemp int,Deleteemp int)where ChangeToemp <> 0)
IN (select e.empid from tblemp o inner join indicator I on e.empid=I.empid where I.userroleid=4)
and
(Select ChangeToemp FROM OPENXML (@empIDdoc, '/empss/Item', 1)WITH (ChangeToemp int,Deleteemp int)where ChangeToemp <> 0)
NOT IN ( select e.empid from tblemp o inner join indicator I on e.empid=I.empid where I.userroleid=4 )
begin
raiserror('Can not do this',16,1)
rollback transaction fixduplicate
return
end
Wen I try to execute the above query in a store proc I get an error for " subquery returning more than 1 value", I understand its doing so but how can I run this with out error but I want the same login here.
June 15, 2007 at 1:27 pm
Can you post DDL and example data of the tables you are referencing and sample XML that is being used in the query?
June 15, 2007 at 3:06 pm
you need to change you If statemets to
IF EXISTS (...) AND NOT EXISTS (...)
BEGIN
raiserror('Can not do this',16,1)
rollback transaction fixduplicate
return
END
* Noel
June 18, 2007 at 8:13 am
but how can i use EXISTS in my query as am looking for some records as
IF (select .......) IN (select....) begin raise error....
June 18, 2007 at 9:32 am
Assuming e.empid should be o.empid, try something like the following:
IF EXISTS (
SELECT *
FROM tblemp o1
WHERE EXISTS (
SELECT *
FROM indicator I1
WHERE o1.empid=I1.empid
AND I1.userroleid=4
)
AND EXISTS (
SELECT *
FROM OPENXML (@empIDdoc, '/emps/Item', 1) WITH(ChangeToemp int,Deleteemp int) X1
WHERE o.empid = X1.DeleteEmp
)
)
AND NOT EXISTS (
SELECT *
FROM tblemp o2
WHERE EXISTS (
SELECT *
FROM indicator I2
WHERE o2.empid=I2.empid
AND I2.userroleid=4
)
AND EXISTS (
SELECT *
FROM OPENXML (@empIDdoc, '/emps/Item', 1) WITH(ChangeToemp int,Deleteemp int) X2
WHERE o2.empid = X2.ChangeToemp
)
)
BEGIN
RAISERROR('Cannot do this', 16, 1)
ROLLBACK
RETURN
END
June 19, 2007 at 9:13 am
I still wonder why my query returns an error saying
" subquery returned more than 1 value. This is not permitted when subquery follows =.!=.<,<=,>,>= or the subquery is used as an expression"
I am just using IN and NOT IN to check if my result set has values in the another result set
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply