2

I am writing a stored procedure in SQL where i have a scenario that fetch all record if parameter is null or fetch matching record if parameter is not null. In this case, i always use ISNULL function like that:

table.value = ISNULL(@param,table.value)

But in this case if value is not null, it works fine, but if value is null then it fetch all record except those where table.value is null. So i searched and found a solution here answered by sII. but i don't understand the statement @param IS NULL OR value= @param It works fine for me but i am unable to understand? How it works? Thanks in advance for answer.

Community
  • 1
  • 1
CodeLover
  • 271
  • 1
  • 3
  • 14
  • It is a two part statement with a OR... param IS NULL is the first conditional so either this is true or the second conditional value = param is true. What is the issue with it? – usamazf Mar 21 '16 at 04:50
  • @UsamaZafar there is no issue. I want to know how it works? – CodeLover Mar 21 '16 at 05:00

1 Answers1

3

Below is my understanding about ALL IF NULL Statement.

Case 1: If the parameter @param IS NULL.

In this case the All if NULL statement becomes like this,

NULL IS NULL OR value= @param.

Here the left part of the OR statement becomes True, So records will fetch according to that part. so the query becomes,

SELECT *FROM TABLE WHERE NULL IS NULL which is same as SELECT *FROM TABLE. So it will fetch all the records.

Case 2: If the parameter @param have a value (say value = 1)

In this case the All if NULL statement becomes like this,

1 IS NULL OR value= 1.

Here the left part of the OR statement becomes False, So records will fetch according to the right part. So the query becomes,

SELECT *FROM TABLE WHERE value= 1.

Hope you understand Now..

bmsqldev
  • 2,627
  • 10
  • 31
  • 65