sql - 在函数 xp_dirtree 上传递变量时出错

Question

我正在尝试编写一个存储过程来收集位于特定存档中的所有文件的名称并将其存储在临时表中。当我编写它并将路径硬编码为函数 xp_dirtree 中的参数时，它会产生我想要的结果。

IF ( SELECT Object_id('tempdb..#archf_names')) IS NOT NULL
BEGIN
  DROP TABLE #archf_names
END

--table to hold file information
CREATE TABLE #archf_names(filename VARCHAR(MAX), depth INT, filetype INT NOT NULL)     

INSERT #archf_names (filename, depth, filetype)
EXEC MASTER.sys.xp_dirtree '\\sqlvm82\ImportsExports\Archive\Archive', 1, 1

SELECT * FROM #archf_names
WHERE filetype <> 0

当我尝试传入一个类型为 VARCHAR 的变量时，该变量已设置为等于上面硬编码的相同路径，我收到此错误“当前命令发生严重错误。结果，如果有的话，应该被丢弃。”

DECLARE @path  AS VARCHAR(MAX) --Path to folder containing archive

SET @path = 'C:\\sqlvm82\ImportsExports\Archive\Archive'

IF ( SELECT Object_id('tempdb..#archf_names')) IS NOT NULL
BEGIN
  DROP TABLE #archf_names
END

IF ( SELECT Object_id('tempdb..#sh_out')) IS NOT NULL
BEGIN
  DROP TABLE #sh_out
END

CREATE TABLE #sh_out(line VARCHAR(MAX)) -- table for xp_cmdshell output
CREATE TABLE #archf_names(filename VARCHAR(MAX) NOT NULL, depth INT NOT NULL, filetype INT NOT NULL) --table to hold file information

INSERT #archf_names (filename, depth, filetype)
EXEC MASTER.sys.xp_dirtree @path, 1, 1
SELECT * FROM #archf_names
WHERE filetype <> 0

还有一些其他功能是此存储过程的一部分，但上面的代码片段是我遇到的问题。我希望我已经足够清楚地解释了自己。

Answer 1

尽量不要为参数使用 LOB 类型。您的 @path 真的需要支持 20 亿个字符吗？将你的 DECLARE 更改为这个对我来说很好用(并且它确实返回有效数据，而不是“空白列”):

DECLARE @path VARCHAR(8000);