Question about CASE

My proc:
CREATE PROCEDURE ListOrderDetails
DECLARE @.idoc INT
DECLARE @.inn AS VARCHAR(1000)
SET @.inn = '
<ROOT>
<OrderDetail num="261"/>
<OrderDetail num="263"/>
<OrderDetail num="264"/>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.inn
select * from orders where id_order in <-- here goes the error
(
case
when len(@.inn) = 0 then (select id_order from orders)
else (select num from openxml(@.idoc,'/ROOT/OrderDetail', 1) WITH (num
int))
end
)
EXEC sp_xml_removedocument @.idoc
The Errorr (pointed out above):
Server: Msg 512, Level 16, State 1, Line 16
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
Can anyone explain me what's going on?
Thanks in advanceTry this (untested)
if len(@.inn) = 0
select * from orders where id_order in (select id_order from
rders) --I'm about what you do here
else
select * from orders where id_order in ( select num from
openxml(@.idoc,'/ROOT/OrderDetail', 1) WITH (num
> int)))
<vcinquini@.gmail.com> wrote in message
news:1145537892.798149.124240@.g10g2000cwb.googlegroups.com...
> My proc:
> CREATE PROCEDURE ListOrderDetails
> DECLARE @.idoc INT
> DECLARE @.inn AS VARCHAR(1000)
> SET @.inn = '
> <ROOT>
> <OrderDetail num="261"/>
> <OrderDetail num="263"/>
> <OrderDetail num="264"/>
> </ROOT>'
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.inn
> select * from orders where id_order in <-- here goes the error
> (
> case
> when len(@.inn) = 0 then (select id_order from orders)
> else (select num from openxml(@.idoc,'/ROOT/OrderDetail', 1) WITH (num
> int))
> end
> )
> EXEC sp_xml_removedocument @.idoc
> The Errorr (pointed out above):
> Server: Msg 512, Level 16, State 1, Line 16
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> Can anyone explain me what's going on?
> Thanks in advance
>|||I' could use your suggestion, but how to use an IF inside a WHERE
clause?

>if len(@.inn) = 0
>select * from orders where id_order in (select id_order from
>rders) --I'm about what you do here
>else
That's because I'm plannig to implement a filter in the future on order
details. When there's no filter passed as parameter, I'd like to
recover all rows (all order details). Where there is, I'd filter using
OPENXML stuff. Did you undertand? Excuse me for my bad English|||There is a difference between a CASE statement and an IF. I think other
people on this group would be better qualified to explain the details,
but if you want your code to just work as though it were an IF in a
WHERE clause, I suppose this could do it (though I've not tested this):
select * from orders where id_order in (
select id_order from orders WHERE len(@.inn)=0
UNION ALL
select num from openxml(@.idoc,'/ROOT/OrderDetail', 1) WITH (num int))
WHERE leb(@.Inn) != 0
)
Cheers
Will