Hi

Yesterday I was writing a stored procedure, where I had to fetch data based on the Null condition. What I had to do was make a null checking in the field. If the value is null then I would take another column or else the current. The same logic would apply for multiple columns.

Hence in one query I had to check 5 columns to fetch the data. At first I had 2 things in mind, Use the case condition to fetch data. Another thing that came to my mind instantly was the isNull function. A simple example would be

Isnull(column1, Isnull(column2, Isnull(column3, Isnull(column4,0))))

This can longer if required. One of my colleague gave me the advice to use the COALESCE function. The function is tailor made for the situation. The function will return the first not null value from the given argument. The function can take N number of argument.

Hence the same select can be written as

Coalesce(column1, column2, column3, column4, 0)

[Note: If all argument are null then the function will return back null, hence I have added 0 as the last argument.]

Thanks
Vikram