How Can we use ISNULL to all Column Names in SQL Server 2008?

Question

I have a question

I tried to google it but looks like they don't like *

I'm using SQL Server 2008.

I have the following database table:

 P_Id   ProductName UnitPrice   UnitsInStock    UnitsOnOrder
------------------------------------------------------------------------
   1    Jarlsberg   10.45                 16    15
   2    Mascarpone  Null                  23    NULL 
   3    Gorgonzola  15.67                  9    20

If I need to replace the null with a string I know I do :

 SELECT ISNULL(UnitsOnOrder,'No Data') FROM tbl

Questions

• How can I use ISNULL() with multi column names ?
• is it possible to use it with *

Like

SELECT ISNULL(* , 'NO data') FROM tbl 

I think this will be tricky because of the datatype, you can't pass string to INT datatype so how can I fix this too

Update

Okay if i use ISNULL() with a datatype of int it will return 0 which will be a value to me , how can i pass empty string instead ?

Answer 1

You can use ISNULL multiple times in the same SQL statement for different columns, but you must write it separately for each column:

SELECT
    ISNULL(ProductName, 'No Data') AS ProductName,
    ISNULL(CAST(UnitPrice AS NVARCHAR), 'No Data') AS UnitPrice, 
    ISNULL(CAST(UnitsInStock AS NVARCHAR), 'No Data') AS UnitsInStock,
    ISNULL(CAST(UnitsOnOrder AS NVARCHAR), 'No Data') AS UnitsOnOrder
FROM tbl

If you are building a dynamic SQL query, you could theoretically gather a list of columns in the table and generate a query with ISNULL on each one. For example:

DECLARE @SQL nvarchar(max)

SET @SQL = 'SELECT '

SELECT @SQL = @SQL + 'ISNULL(CAST([' + sc.name + '] AS NVARCHAR), ''No Data'') AS [' + sc.name + '],'
FROM sys.objects so
INNER JOIN sys.columns sc ON sc.object_id = so.object_id
WHERE so.name = 'tbl'

-- Remove the trailing comma
SELECT @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ' FROM tbl'

EXEC sp_sqlexec @SQL

This code has problems when converting some column types like timestamps to an nvarchar, but it illustrates the technique.

Note that if you had another column that should be returned if a value is null, you could use the COALESCE expression like this:

SELECT COALESCE(ProductName, P_Id) AS Product...

Answer 2

Try this...

ISNULL (COALESCE (column1, column2), 'No Data')

You would need to include all column names though, you can't use *

COALESCE returns the first non-null value in its argument list so if they are all null it will return null