T-SQL While 循环和串联
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/451834/
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
T-SQL While Loop and concatenation
提问by JustinT
I have a SQL query that is supposed to pull out a record and concat each to a string, then output that string. The important part of the query is below.
我有一个 SQL 查询，它应该提取一条记录并将每个记录连接到一个字符串，然后输出该字符串。查询的重要部分如下。
DECLARE @counter int;
SET @counter = 1;
DECLARE @tempID varchar(50);
SET @tempID = '';
DECLARE @tempCat varchar(255);
SET @tempCat = '';
DECLARE @tempCatString varchar(5000);
SET @tempCatString = '';
WHILE @counter <= @tempCount
BEGIN
SET @tempID = (
SELECT [Val]
FROM #vals
WHERE [ID] = @counter);
SET @tempCat = (SELECT [Description] FROM [Categories] WHERE [ID] = @tempID);
print @tempCat;
SET @tempCatString = @tempCatString + '<br/>' + @tempCat;
SET @counter = @counter + 1;
END
When the script runs, @tempCatStringoutputs as null while @tempCatalways outputs correctly. Is there some reason that concatenation won't work inside a While loop? That seems wrong, since incrementing @counterworks perfectly. So is there something else I'm missing?
脚本运行时，@tempCatString输出为空，但@tempCat始终正确输出。是否有某种原因使连接在 While 循环中不起作用？这似乎是错误的，因为递增@counter工作完美。那么还有什么我想念的吗？
回答by HLGEM
Looks like it should work but for somereason it seems to think @tempCatString is null which is why you are always getting a null value as nullconcatenated to anything else is still null. Suggest you try with COALESCE()on each of the variables to set them to " " if they are null.
看起来它应该可以工作，但出于某种原因，它似乎认为@tempCatString 为空，这就是为什么你总是得到一个空值，因为空连接到其他任何东西仍然是空。建议您尝试使用COALESCE()每个变量将它们设置为“”，如果它们为空。
回答by keithwarren7
this would be more efficient....
这样效率会更高......
select @tempCatString = @tempCatString + Coalesce(Description,'') + '<br/>' from Categories...
select @fn
also look at concat_null_yields_null as an option to fix your concatenation issue, although I would avoid that route
还可以将 concat_null_yields_null 作为解决串联问题的选项，尽管我会避免该路线
回答by MatBailie
I agree with keithwarren, but I would always be sure to add an ORDER BY clause to the query. You can then be sure as to exactly what order the values are being concatenated in.
我同意 keithwarren 的观点，但我总是确保在查询中添加 ORDER BY 子句。然后，您可以确定这些值的连接顺序。
Also, the COALESCE to replace the NULL value with '' will effectively yield blank rows. I don't know if you want them or not, but if not just filter in the WHERE clause instead...
此外，将 NULL 值替换为 '' 的 COALESCE 将有效地产生空白行。我不知道你是否想要它们，但如果不只是在 WHERE 子句中过滤......
Finally, you appear to have a temp table including the IDs you're interested in. This table can just be included in a JOIN to filter the source table...
最后，您似乎有一个包含您感兴趣的 ID 的临时表。该表可以包含在 JOIN 中以过滤源表...
DELCARE @output VARCHAR(8000)
SET @output = ''
SELECT
@output = @output + [Categories].Description + '<br/>'
FROM
Categories
INNER JOIN
#vals
ON #vals.val = [Categories].ID
WHERE
[Categories].Description IS NOT NULL
ORDER BY
[Categories].Description
