我们如何对 SQL Server 2008 中的所有列名使用 ISNULL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13237736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How Can we use ISNULL to all Column Names in SQL Server 2008?
提问by Mina Gabriel
I have a question
我有个问题
I tried to googleit but looks like they don't like *
我试过了google，但看起来他们不喜欢*
I'm using SQL Server 2008.
我正在使用 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 nullwith a string I know I do :
如果我需要null用字符串替换，我知道我会这样做：
SELECT ISNULL(UnitsOnOrder,'No Data') FROM tbl
Questions
问题
- How can I use
ISNULL()with multi column names ? - is it possible to use it with
*
- 如何使用
ISNULL()多列名称？ - 是否可以与它一起使用
*
Like
喜欢
SELECT ISNULL(* , 'NO data') FROM tbl
I think this will be tricky because of the datatype, you can't pass string to INTdatatype so how can I fix this too
我认为这会很棘手，因为数据类型，你不能将字符串传递给INT数据类型，所以我该如何解决这个问题
Update
更新
Okay if i use ISNULL() with a datatype of intit will return 0which will be a value to me , how can i pass empty string instead ?
好的，如果我使用ISNULL() 的数据类型，int它将返回0对我来说是一个值，我该如何传递空字符串呢？
回答by Paul Williams
You can use ISNULL multiple times in the same SQL statement for different columns, but you must write it separately for each column:
可以在同一个 SQL 语句中对不同的列多次使用 ISNULL，但必须为每一列单独编写：
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:
如果您正在构建动态 SQL 查询，理论上您可以收集表中的列列表，并在每个列上生成一个带有 ISNULL 的查询。例如：
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.
此代码在将某些列类型（如时间戳）转换为 nvarchar 时存在问题，但它说明了该技术。
Note that if you had another column that should be returned if a value is null, you could use the COALESCEexpression like this:
请注意，如果您有另一列应在值为 null 时返回，您可以使用COALESCE表达式，如下所示：
SELECT COALESCE(ProductName, P_Id) AS Product...
回答by Rich Andrews
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
COALESCE 返回其参数列表中的第一个非空值，因此如果它们都为空，它将返回空
