MS SQL Server MSEQ - Is Stored Procedure and 'in' broken in MSDE 2000/SQL 2000 SP4?

Author

Is Stored Procedure and 'in' broken in MSDE 2000/SQL 2000 SP4?

Tuner Fich

2005-06-21, 11:23 am

This is not the command that im running but demonstrates the problem just
fine, basically 'in' using statored procedures seems to be performing as 'in'
or 'is' where as sending a query direct (identical) only performs 'in' as
expected

Example

Use msdb
Select * From Sysalerts Where database_name in (Select
convert(sysname(128)
,null))

returns nothing

but put the select command in a stored proceedure and it will return all the
rows with database_name that is null

Am i not seeing/setting something?

Simon

Hugo Kornelis

2005-06-27, 1:23 pm

On Tue, 21 Jun 2005 08:36:05 -0700, "Tuner Fich" <Tuner
Fich@discussions.microsoft.com> wrote:

>This is not the command that im running but demonstrates the problem just
>fine, basically 'in' using statored procedures seems to be performing as 'in'
>or 'is' where as sending a query direct (identical) only performs 'in' as
>expected
>
>Example
>
>Use msdb
>Select * From Sysalerts Where database_name in (Select
> convert(sysname(128)
,null))
>
>returns nothing
>
>but put the select command in a stored proceedure and it will return all the
>rows with database_name that is null
>
>Am i not seeing/setting something?
>
>Simon

Hi Simon,

I guess that you normally have the setting SET ANSI_NULLS ON (which is
fine, as it makes SQL Server treat NULLS as defined in the ANSI
standard, making your code more portable). However, when creating stored
procedure, someow the setting gets changed to SET ANSI_NULLS OFF (which
is definitely NOT fine, as it makes SQL Server treat NULLS in a
non-standard way that might appear logical at first glance but is not,
and that will make other database programmers fail to understand your
code).

With the ANSI standard ebhaviour for NULLS, logical expressions use
three-valued logic (True, False and Unknown) and all comparisons to NULL
will always return Unknown. The only valid way to copmpare a column or
variable to NULL is to use "WHERE column IS [NOT] NULL".

Also, stop using [NOT] IN with a subselect, as they are a source of
confusion with ANSI standard settings, and they can always be
transformed into a [NOT] EXISTS subquery that usually performs better as
well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links