Code bellow is not working, any ideas why?
代码吼叫不起作用,任何想法为什么?
declare @Counter int
set @Counter = 0
declare @ConcText nvarchar(1000)
while @Counter < 5
begin
--set @ConcText = @ConcText + cast(@Counter as nvarchar(10)) + N' counter,'
--set @ConcText = @ConcText + convert(nvarchar(10), @Counter) + N' counter,'
set @ConcText = @ConcText + N' counter,'
set @Counter = @Counter + 1
end
print @ConcText --<-- this is null, why ??
2 个解决方案
#1
See MSDN: + (String Concatenation) (Transact-SQL):
请参阅MSDN:+(字符串连接)(Transact-SQL):
Just like arithmetic operations that are performed on null values, when a null value is added to a known value the result is typically an unknown value, a string concatenation operation that is performed with a null value should also produce a null result.
就像对空值执行的算术运算一样,当将空值添加到已知值时,结果通常是未知值,使用空值执行的字符串连接操作也应该产生空结果。
So to get things work, it's a good practice to initiate varchar variables immediatly after declare:
因此,为了使事情有效,在声明后立即启动varchar变量是一个好习惯:
DECLARE @ConcText NVARCHAR(1000)
SET @ConcText = ''
Other way to handle NULL concat issue (in case you don't know if value is NULL or not) - ISNULL or COALESCE:
处理NULL concat问题的其他方法(如果你不知道值是否为NULL) - ISNULL或COALESCE:
SET @ConcText = ISNULL(@ConcText, '') + N' counter,'
SET @ConcText = COALESCE(@ConcText, '') + N' counter,'
#2
You are not setting @ConcText to anything at the start, therefore when you concatenate anything to NULL you get NULL.
您没有在开始时将@ConcText设置为任何内容,因此当您将任何内容连接到NULL时,您将获得NULL。
#1
See MSDN: + (String Concatenation) (Transact-SQL):
请参阅MSDN:+(字符串连接)(Transact-SQL):
Just like arithmetic operations that are performed on null values, when a null value is added to a known value the result is typically an unknown value, a string concatenation operation that is performed with a null value should also produce a null result.
就像对空值执行的算术运算一样,当将空值添加到已知值时,结果通常是未知值,使用空值执行的字符串连接操作也应该产生空结果。
So to get things work, it's a good practice to initiate varchar variables immediatly after declare:
因此,为了使事情有效,在声明后立即启动varchar变量是一个好习惯:
DECLARE @ConcText NVARCHAR(1000)
SET @ConcText = ''
Other way to handle NULL concat issue (in case you don't know if value is NULL or not) - ISNULL or COALESCE:
处理NULL concat问题的其他方法(如果你不知道值是否为NULL) - ISNULL或COALESCE:
SET @ConcText = ISNULL(@ConcText, '') + N' counter,'
SET @ConcText = COALESCE(@ConcText, '') + N' counter,'
#2
You are not setting @ConcText to anything at the start, therefore when you concatenate anything to NULL you get NULL.
您没有在开始时将@ConcText设置为任何内容,因此当您将任何内容连接到NULL时,您将获得NULL。