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

On Tue, 21 Jun 2005 08:36:05 -0700, "Tuner Fich" <Tuner
Fich@xxxxxxxxxxxxxxxxxxxxxxxxx> 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)
.

Relevant Pages

  • TIP #185: Null Handling
    ... nulls, and command modifications for manipulating them. ... Tcl deals with strings, the universal medium for representing data. ... is know and it is an empty string, but if a respondent forgets to give ...
    (comp.lang.tcl)
  • Re: Pass CString array
    ... delimited with nulls, and ending with a null, ... The function I'm dealing with ... He details the command line ... it's sometimes written as char **var and sometimes ...
    (microsoft.public.vb.general.discussion)
  • Re: ascii nulls, awk, and tr
    ... > them in awk already, so adding something to an awk script would be a ... don't know how well it compares or whether it can handle nulls. ... can handle, nulls just like other characters, etc. ... reading the terminal after you typed that command). ...
    (comp.unix.solaris)
  • Re: Beginner needs help inserting a row
    ... I'm getting an exception on my row add command: ... Column 'uniqueidentifier' does not allow nulls. ...
    (microsoft.public.dotnet.framework.adonet)
  • Is Stored Procedure and in broken in MSDE 2000/SQL 2000 SP4?
    ... This is not the command that im running but demonstrates the problem just ... basically 'in' using statored procedures seems to be performing as 'in' ... Prev by Date: ...
    (microsoft.public.sqlserver.mseq)