为什么 NVarchar(MAX) 无法使用 SP_ExecuteSQL 执行完整的动态查询?

Question

我创建了一个存储过程，它将有两个查询，这两个查询将更新两个表中的记录。

所以我会提到它显示的内容

我的程序如下

ALTER PROC UpdateMissingItemONPoe 
@StoreID INT,
@Name VARCHAR(20)
AS
BEGIN
DECLARE @UpdateRec NVARCHAR(MAX) = '';
DECLARE @StoreIP SYSNAME = ''
SET @StoreIP = CASE @StoreID 
WHEN 1111 THEN '[192.168.1.45].[Store1].[dbo]'
WHEN 2222 THEN '[192.168.2.45].[Store2].[dbo]'
WHEN 3333 THEN '[192.168.3.45].[Store3].[dbo]'
END
SET @UpdateRec = N' IF((SELECT NameID from '+@StoreIP+'.Details WHERE Name = '''+@Name+''') = 2 )
BEGIN
INSERT INTO ' + @StoreIP + '.PurchaseOrderEntry(
/*  InsertField Name  */
)
select
Name,
Standard,
Department,
Category,
SubDescription,
Rank
from
'+@StoreIP+'.Details 
where
Name = '''+@Name+'''
INSERT INTO '+@StoreIP+'.HeadQuarter(
/*  InsertField Name  */
)
select
Name,
Standard,
Department,
Category,
SubDescription,
Rank
from
'+@StoreIP+'.Details 
where
Name = '''+@Name+'''
END'
print @UpdateRec
EXEC sp_executesql @UpdateRec
END

当我执行此存储过程时，它没有显示任何错误消息。但是，当我打印时，该动态查询没有完整的查询。

打印查询类似于

INSERT INTO '+@StoreIP+'.PurchaseOrderEntry(
/*  InsertField Name  */
)
select
Name,
Standard,
Department,
Category,
SubDescription,
Rank
from
'+@StoreIP+'.Details 
where
Name = '''+@Name+'''
INSERT INTO '+@StoreIP+'.HeadQuarter(
/*  InsertField Name  */
)
select
Name,
Standard,
Department,
Category,     

缺少其余行

Answer 1

您的查询肯定超过 4000 个字符。

将 SET 语句更改为

SET @UpdateRec = CONVERT(NVARCHAR(MAX), '') + N' IF(('

它会强制您的查询字符串N' IF ((...为 nvarchar(max(。

Answer 2

我会把它改写为：

ALTER PROC UpdateMissingItemONPoe 
@StoreID INT,
@Name VARCHAR(20)
AS
BEGIN
DECLARE @UpdateRec NVARCHAR(MAX) = N'';
DECLARE @StoreIP NVARCHAR(128) = N'';
SET @StoreIP = CASE @StoreID 
WHEN 1111 THEN '[192.168.1.45].[Store1].[dbo]'
WHEN 2222 THEN '[192.168.2.45].[Store2].[dbo]'
WHEN 3333 THEN '[192.168.3.45].[Store3].[dbo]'
END;
SET @UpdateRec = N' IF((SELECT NameID from <@StoreIP>.Details 
WHERE Name = @Name) = 2 )
BEGIN
INSERT INTO <StoreIP>.PurchaseOrderEntry(
/*  InsertField Name  */
)
select
Name,
Standard,
Department,
Category,
SubDescription,
Rank
from
<@StoreIP>.Details 
where
Name = @Name
INSERT INTO <@StoreIP>.HeadQuarter(
/*  InsertField Name  */
)
select
Name,
Standard,
Department,
Category,
SubDescription,
Rank
from
<@StoreIP>.Details 
where
Name = @Name
END';
SET @UpdateRec = REPLACE(@UpadteRec, N'<@StoreIP>', @StoreIp);
print @UpdateRec
EXEC sp_executesql @UpdateRec, N'@Name NVARCHAR(20)', @Name;
END

使用参数化的动态 SQL 并使用REPLACE而不是串联交换表名。

Answer 3

过去，如果这些部分没有显式转换为 nvarchar(max( 类型，SQL Server 会使用这些部分语句将这些语句转换为 nvarchar(4000(。

所以如果你有一个变量

DECLARE @UpdateRec NVARCHAR(MAX) = '';

然后使用这种类型的语法

SET @UpdateRec = N' IF((SELECT NameID from '+@StoreIP+'.Details WHERE
Name = '''+@Name+''') = 2 ) BEGIN
INSERT INTO ' + @StoreIP + '.PurchaseOrderEntry( (...)'

结果将以静默方式转换为 nvarchar(4000(，并将在不发出警告的情况下截断任何多余的字符。