sp

[fmardani]

In vb.net i am running a stored procedure in sql server 2000
the SP is something like:

@Curve int
.
.
.
@Qualifier varchar(3) = NULL
AS
select * from table1 where
curve=@Curve
.
.
.
AND Qualifier = @Qualifier

Basically the question is that when i run the query like select * from table1 where Qualifier IS NULL
it works and returns data but in SP when no @Qualifier is passed then it becomes select * from table1 where Qualifier = NULL

when it is = null it does not return data
and thoughts?
thanks

 

[JohnEfford]

In SQL null does not equal any value ie

Null = Null returns false

as this clause needs to be true to return a row, no rows are returned.

To test for Null use Is Null

There for:

..and (Qualifier = @Qualifier or @Qualifier is Null)

Regards
John

 

[sunaj]

???
------------------
declare @Q varchar(3)
set @Q=NULL
select 1 where @Q=null
-------------------
Returns 1 and therefore contradicts JohnEffords explanation.

When you use a default value in the variable declaration of a sp you need to call the sp with 'default' in order to assign the default value to the variable - do you do that?
(exec SP_Example 1,default)

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[LFCfan]

Hey there folks

Sunaj and John - your databases or Query Analyser connections must have different ANSI_NULLS settings.
run the following, and you'll see what i mean. i've included the relevant bit from SQL Server Books Online (and MSDN) below the line of asterisks.

fmardani - you could ask your database guys to change the setting on the database (ie set ansi_nulls to OFF), but it could affect a lot of under-the-cover stuff (potentially) so they might not want to do it - in which case you could use John's solution. Alternatively, there may be a way to set the ansi_nulls off (and then on again - not sure if this would be necessary) via VB, but i'm afraid i dont know what, or even if there is one!

hope this helps

set ansi_nulls off
declare @Q varchar(3)
set @Q=NULL
select 1 where @Q=null

set ansi_nulls on
--declare @Q varchar(3)
set @Q=NULL
select 1 where @Q=null

********************************************************
ANSI_NULLS

When set to ON, all comparisons to a null value evaluate to NULL (unknown). When set to OFF, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL. By default, the ANSI_NULLS database option is OFF.

 

[omarmtz]

another option... why don-t you use a if... else...

if @Qualifier is null
begin
select * from ... where Qualifier is null
end
else
begin
select * from ... where Qualifier = @Qualifier
end