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 ?

Why do you need to use *? Does it not work for you if you have the columns in the field list. — Mikael Eriksson
– Mikael Eriksson, Commented Nov 5, 2012 at 18:05
@MikaelEriksson He doesn't want to wrap every column in the select list with a call to isnull. Trying to cure a symptom, I feel... — Tony Hopkinson
– Tony Hopkinson, Commented Nov 5, 2012 at 18:10
NO i'm working on application from where we collect the SQL quires from the client side and pass this query as string to the server as string the EXEC this string , bringing the data to the client side so i'm expecting some one pass * to the SP on the code behinde i identify all my data return as string , i think i should fix this on the code behind — Mina Gabriel
– Mina Gabriel, Commented Nov 5, 2012 at 18:11
@MinaGabriel - Doing this when the results are displayed is definitely the right approach here.. Data layer should always be abstracted from the presentation layer. — Mike Christensen
– Mike Christensen, Commented Nov 5, 2012 at 18:12

Paul Williams · Accepted Answer · 2012-11-05 18:59:55Z

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...

Prashant Pimpale · Accepted Answer · 2019-06-14 07:28:58Z

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

edited Jun 14, 2019 at 7:28

Prashant Pimpale

10.7k10 gold badges52 silver badges88 bronze badges

answered Nov 5, 2012 at 18:42

Rich Andrews

4,1883 gold badges38 silver badges48 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related