Geek Answers Handbook

TSQL - ISNULL over multiple columns

I have a simple SQL query (SQL Server 2005) in which I select from a table containing multiple columns with BIT values. These columns are NULL, so they can contain NULL, 0 or 1.

There are quite a few of these columns, and in my query I want to return zero if the value is NULL. I am currently using ISNULL as follows:

SELECT Name, Age, ISNULL(LikesOranges,0), ISNULL(LikesApples,0), ISNULL(LikesPears,0)
FROM FoodPreferences

As I mentioned, there are many of these BIT columns (much more than in the simple example above). Is there a way that I can use ISNULL for multiple columns, such as:

SELECT ISNULL(*,0) FROM FoodPreferences

The above query does not work, but you get what I am trying to do, so I can avoid having to write an ISNULL statement for each column,

Thank.

+6
6

ISNULL ,

select. system_type_id = 104 bit.

select stuff((select  ', isnull('+name+', 0)'
              from sys.columns
              where object_id = object_id('FoodPreferences') and 
                    system_type_id = 104
              for xml path('')), 1, 1, '')

:

-------------------------------------------------------------------------
 isnull(LikesOranges, 0), isnull(LikesApples, 0), isnull(LikesPears, 0)
+7

:

SELECT COALESCE(LikesOranges, LikesApples, LikesPears) AS MyBit FROM FoodPreferences

. NULL, NULL.

UPDATE:

:

SELECT ISNULL(COALESCE(LikesOranges, LikesApples, LikesPears),0) AS MyBit FROM FoodPreferences
+7

. , ISNULL. ,

.

CREATE VIEW vwFoodPreferences
AS
SELECT Name, 
       Age, 
       ISNULL(LikesOranges,0) AS LikesOranges, 
       ISNULL(LikesApples,0) AS LikesApples, 
       ISNULL(LikesPears,0) AS LikesPears
FROM   FoodPreferences
+3

, no.

sql , , sql ISNULL(a,0), ISNULL(b,0), ISNULL(c,0), ISNULL(d,0), etc

+2

I think you can write a simple program and generate a select clause by reading all the columns and forming select

+1
source

not yet this:

SELECT COALESCE(LikesOranges, LikesApples, LikesPears, 0) AS MyBit FROM FoodPreferences

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017

-1
source

More articles:


All Articles