CODEWITHSUNDEEP
sqlsybaseisnull
Gui
Gui

Reputation: 27

SQL isnull error

While trying to do a select query using the isnull which, i've tried in 2 differents servers that are identical one to the other. (They both use the same procedure, dll, return page, they just change from one ip to the other)

SELECT * FROM
ITEM_TEST
WHERE ITEM_NAME = isnull(@ITEM_TESTE, ITEM_NAME)

The operation is working without problems in one of the servers, returning all options when the @ITEM_TESTE is NULL, while in the other, it returns ONLY the ones that are NOT NULL. I'm using a sybase-based-application (version 12.5) called SQLdbx (version 3.14)

Case it's not so openly understood, @ITEM_TESTE is a variable given from the user that is optional, meaning it can be null where the ITEM_NAME accepts a STRING to it, while it's also option the ITEM_TEST is a table with more than 10 variables, i'm simplifing it. This search, however, want's all the possibles results even if ITEM_NAME is UNKOWN while using others variables to narrow down the search. (I thought about creating a search with an IF condition that excluded ITEM_NAME and it worked, but the it made the search so "laggy" due to perfomance issues.)

EDIT

Change the name of the variables to make it less confusing (both with the same name) and added an explaining for easier understanding

Also, due to copyright issues that i can't post the exact code here.

Upvotes: 1

Views: 821

Answers (2)

Sabre
Sabre

Reputation: 2400

The way it was explained to me and has forever stuck after all of these years, is that NULL is not nothing, it is unknown, so you cannot use an equality check to verify two things you know nothing about are equally nothing. IS is checking that they are in the same unknown state, which has nothing to do with a value.

So as the others have said = NULL will never work, because = implies value comparison.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269613

This is your where clause:

WHERE ITEM_TESTE = isnull(@ITEM_TESTE, ITEM_TESTE)

This where clause will never be true when ITEM_TESTE is NULL, because NULL = NULL evaluates to not true in the SQL world.

Presumably, you want:

WHERE (ITEM_TEST = @ITEM_TESTE OR @ITEM_TESTE IS NULL)

Upvotes: 1

Related Questions