变量应该被声明

Question

我使用的是T-SQL。目标是在数据库中插入多个文件。如果我不使用循环，它工作得很好。

在循环中，我总是得到这样的错误:

@InputXML应该被声明

我代码:

IF OBJECT_ID('TEMPDB..#TEMP_FILES') IS NOT NULL 
DROP TABLE #TEMP_FILES
CREATE TABLE #TEMP_FILES
(
FileName VARCHAR(MAX),
DEPTH VARCHAR(MAX),
[FILE] VARCHAR(MAX)
)

INSERT INTO #TEMP_FILES
EXEC master.dbo.xp_DirTree '\MyServerMyFolder',1,1

DELETE FROM #TEMP_FILES WHERE RIGHT(FileName,4) != '.XML'

--
SET QUOTED_IDENTIFIER ON 
GO

TRUNCATE Table MyTable2
DECLARE @InputXML XML
DECLARE @FILENAME VARCHAR(MAX),@SQL VARCHAR(MAX)

WHILE EXISTS(SELECT * FROM #TEMP_FILES)
BEGIN
SET @FILENAME = (SELECT TOP 1 FileName FROM #TEMP_FILES)
SET @sql = 'SELECT @InputXML = CAST(x AS XML) FROM OPENROWSET(BULK \MyServerMyFolder'''+ @FILENAME +''', SINGLE_BLOB) AS T(x)

INSERT INTO MyTable2 ([id],[version], [name], [listId], [listCode])
SELECT 
product.value(''(@id)[1]'', ''NVARCHAR(10)''), 
product.value(''(@version)[1]'', ''NVARCHAR(14)''), 
product.value(''(name[1])'', ''NVARCHAR(255)''),
product.value(''(listId[1])'', ''NVARCHAR(9)''),
product.value(''(listCode[1])'', ''NVARCHAR(10)'')
FROM @InputXML.nodes(''xxx/values/value'') AS X(product)'
EXEC(@SQL)

DELETE FROM #TEMP_FILES 
WHERE FileName = @FILENAME
END

Answer 1

您需要在动态SQL中声明变量(应该是nvarchar而不是varchar)。您还应该使用QUOTENAME来确保文件名没有问题:

DECLARE @sql nvarchar(max) = N'
DECLARE @InputXML XML;
SELECT @InputXML = CAST(x AS XML) FROM OPENROWSET(BULK ' + QUOTENAME(N'\MyServerMyFolder' + @FILENAME, '''') + N', SINGLE_BLOB) AS T(x)

INSERT INTO MyTable2 ([id],[version], [name], [listId], [listCode])
SELECT 
product.value(''(@id)[1]'', ''NVARCHAR(10)''), 
product.value(''(@version)[1]'', ''NVARCHAR(14)''), 
product.value(''(name[1])'', ''NVARCHAR(255)''),
product.value(''(listId[1])'', ''NVARCHAR(9)''),
product.value(''(listCode[1])'', ''NVARCHAR(10)'')

FROM @InputXML.nodes(''xxx/values/value'') AS X(product)'

我会说，虽然，我敦促你找到另一种方法来加载文件到SQL Server。动态OPENROWSET，尤其是来自用户输入的，是不可取的。可以选择批量插入或BCP。