SQL 不为空时的Sql Case语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9100207/
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
Sql Case Statement when is not null
提问by jcvegan
I want to generate declaration of variables dynamically depending on the table. I mean, I want to declare variables of a table, each variable must be the same type as its column I'm doing something like that but the result only works if the type is not an int
我想根据表动态生成变量声明。我的意思是，我想声明一个表的变量，每个变量必须与其列的类型相同我正在做类似的事情，但结果仅在类型不是 int 时才有效
Select 'Declare @Doc' + COLUMN_NAME + ' '+DATA_TYPE+case(CHARACTER_MAXIMUM_LENGTH) when Null then ' ' else '(' +convert(varchar(12),CHARACTER_MAXIMUM_LENGTH) + ')' end
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'Documentos'
And my result is this
我的结果是这样的
(No column name)
NULL
Declare @DocSerie varchar(5)
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Declare @DocImporteLetras varchar(255)
Declare @DocMotivos text(2147483647)
NULL
Declare @DocDocumentosReferencia varchar(255)
NULL
NULL
Declare @DocAuditoriaIPC varchar(40)
NULL
NULL
Declare @DocAuditoriaIPM varchar(40)
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
回答by MatBailie
CASE x WHEN null THENis the same as CASE WHEN x = null THEN. But nothing equals null in that way. This means that you are always getting the ELSE part of your CASE statement. And that means that you are trying to concatenate a string with NULL, which always yields NULL.
CASE x WHEN null THEN与 相同CASE WHEN x = null THEN。但是这样就没有什么等于 null 了。这意味着您总是得到 CASE 语句的 ELSE 部分。这意味着您试图将一个字符串与 NULL 连接起来，这总是会产生 NULL。
You need CASE WHEN x IS NULL THENinstead...
你需要CASE WHEN x IS NULL THEN代替...
SELECT
'Declare @Doc'
+ COLUMN_NAME + ' '
+ DATA_TYPE
+ CASE WHEN (CHARACTER_MAXIMUM_LENGTH) IS Null then ' ' else '(' convert(varchar(12),CHARACTER_MAXIMUM_LENGTH) + ')' end
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Documentos'
回答by Pankaj
SELECT 'Declare @Doc'
+ COLUMN_NAME + ' '
+ DATA_TYPE + Coalesce('(' + CHARACTER_MAXIMUM_LENGTH + ')', '')
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Documentos'
SELECT 'Declare @Doc'
+ COLUMN_NAME + ' '
+ DATA_TYPE
+ Case When CHARACTER_MAXIMUM_LENGTH Is Null Then '' Else '(' + Convert(Varchar, CHARACTER_MAXIMUM_LENGTH) + ')' End
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Documentos'
