EXISTS

Why can't I do this?
DECLARE @.BIT BIT
SELECT @.BIT = EXISTS( SELECT 1 FROM sysobjects )
I can do...
IF (EXISTS(...))
SELECT .. FROM .. WHERE EXISTS(..)
CASE WHEN EXISTS(...) THEN ... END
So why not @.BIT = EXISTS( ... ) ?EXISTS is a predicate -- it does not resolve to a value, but rather to a
boolean. But this is only internally -- there is no boolean type in T-SQL
exposed to users. BIT is not a boolean type.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:437b5fe7$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> Why can't I do this?
> DECLARE @.BIT BIT
> SELECT @.BIT = EXISTS( SELECT 1 FROM sysobjects )
> I can do...
> IF (EXISTS(...))
> SELECT .. FROM .. WHERE EXISTS(..)
> CASE WHEN EXISTS(...) THEN ... END
>
> So why not @.BIT = EXISTS( ... ) ?
>
>|||>> Why can't I do this?
In a nutshell, they are incompatible. The EXISTS clause in SQL may return
one of 3 values -- TRUE, FALSE or UNKNOWN. A BIT variable can have only two
values 1 or 0 and an optional NULL.
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23yRtI6t6FHA.1188@.TK2MSFTNGP12.phx.gbl...
> In a nutshell, they are incompatible. The EXISTS clause in SQL may return
> one of 3 values -- TRUE, FALSE or UNKNOWN. A BIT variable can have only
> two
Can you show an example of when EXISTS would return unknown?
Isn't the presence of any rows -- NULL or not -- sufficient for a true
condition?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Try:
SET ANSI_NULLS ON
SELECT 1
WHERE NULL + 3 = NULL ;
Anith|||How does that apply to EXISTS?
The following works just as I would expect:
SET ANSI_NULLS ON
SELECT 'this selects'
WHERE NOT EXISTS
(
SELECT 1
WHERE NULL + 3 = NULL
) ;
If that evaluated to unknown, I would not expect 'this selects' to be
returned.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ueAGpDu6FHA.632@.TK2MSFTNGP10.phx.gbl...
> Try:
> SET ANSI_NULLS ON
> SELECT 1
> WHERE NULL + 3 = NULL ;
> --
> Anith
>|||>> How does that apply to EXISTS?
Ah, true. I was just showing an UNKNOWN can be returned from a 3VL
expression.
Your example is essentially: EXISTS( UNKNOWN ) = UNKNOWN. Since NOT UNKNOWN
= UNKNOWN, it is correct.
Anith