為什么宣告為NVARCHAR(MAX)的變數會丟棄字串的塊？

無論出于何種原因，查詢都被構建為字串并傳遞給另一個存盤程序執行。

查詢量很大。

超過一千行，我們遇到了一個需要我除錯它的問題。

該查詢被構建到一個宣告的NVARCHAR(MAX)變數中，但是當我使用以下命令列印它時發生了一些奇怪的事情 -

WHILE @Printed < @ToPrint BEGIN 
    PRINT(SUBSTRING(
        @sql, @Printed, 4000))
    SET @Printed = @Printed   4000
    PRINT('Printed: '   CONVERT(VARCHAR, @Printed))
END

在列印訊息的某個位置，它只是……丟了一大塊，我不明白為什么。NVARCHAR(MAX)應該能夠保持戰爭與和平超過 100 次，并且這個查詢不是戰爭與和平。

我知道PRINT(...)一次只能列印 4000 個字符的限制（因此是回圈），但這并不能解釋為什么@sql變數只是在某些地方丟失了一個塊。

如果它有幫助，具體來說，在列印前 4,000 個字符后，塊丟棄的位置大約是 1,600 個字符。

為什么要這樣做？我是否錯過了在查詢開始時設定系統變數（如 NOCOUNT 或 ARITHABORT？我什至不知道它們是做什么的，或者它們是否參與其中。

編輯：MCVE：在這里。要重現，請將其復制粘貼到 Microsoft SQL Server Management Studio 并點擊“F5”。列印的訊息將不包括整個@sql。

uj5u.com熱心網友回復：

這對我來說很好用：

DECLARE @sql nvarchar(max) = 
    REPLICATE(CONVERT(nvarchar(max), N'a'), 4000)
    REPLICATE(CONVERT(nvarchar(max), N'b'), 4000)
    REPLICATE(CONVERT(nvarchar(max), N'c'), 4000)
    REPLICATE(CONVERT(nvarchar(max), N'd'), 4000)
    REPLICATE(CONVERT(nvarchar(max), N'e'), 4000)
    REPLICATE(CONVERT(nvarchar(max), N'f'), 4000)
    REPLICATE(CONVERT(nvarchar(max), N'g'), 4000)
    REPLICATE(CONVERT(nvarchar(max), N'h'), 4000)
    REPLICATE(CONVERT(nvarchar(max), N'i'), 4000);


PRINT LEN(@sql);  -- characters
PRINT DATALENGTH(@sql); -- bytes
PRINT '';

DECLARE @Printed int = 1, @ToPrint int = LEN(@sql);

WHILE @Printed < @ToPrint BEGIN 
    PRINT(SUBSTRING(
        @sql, @Printed, 4000))
    SET @Printed = @Printed   4000
    PRINT('Printed: '   CONVERT(varchar(11), @Printed)) -- *
END

*始終指定長度。

輸出是：

36000
72000

aaaaaaaaaa... 4000 As ...aaa
Printed: 4001
bbbbbbbbbb... 4000 Bs ...bbb
Printed: 8001
cccccccccc... 4000 Cs ...ccc
Printed: 12001
dddddddddd... 4000 Ds ...ddd
Printed: 16001
eeeeeeeeee... 4000 Es ...eee
Printed: 20001
ffffffffff... 4000 Cs ...fff
Printed: 24001
gggggggggg... 4000 As ...ggg
Printed: 28001
hhhhhhhhhh... 4000 Bs ...hhh
Printed: 32001
iiiiiiiiii... 4000 Cs ...iii
Printed: 36001

所以，我認為問題出在其他地方。無論如何，這是驗證動態 SQL 內容的一種非常草率的方法。相反，我會這樣做：

SELECT CONVERT(xml, @sql);

然后您可以單擊輸出單元格，它會在 XML 文本編輯器中打開以供查看（如果您想要 IntelliSense 或任何執行的機會，您可以將該輸出復制并粘貼到查詢視窗中，但您必須替換編碼字符像>--> >。我在這里談論這種方法（和另一種方法）：

• 驗證大型動態 SQL 字串的內容

如果您堅持以這種砌磚的方式進行操作，那么此時可能存在某種非列印或字串終止字符。如果你說它在字符 5,600 左右，那么你可以這樣做：

DECLARE @i int = 5550, @c nchar(1);
WHILE @i <= 5650
BEGIN
  PRINT '';
  SET @c = SUBSTRING(@sql, @i, 1);
  PRINT '------   '   RTRIM(@i)   '------:';
  PRINT 'Raw:     '   @c;
  PRINT 'ASCII:   '   ASCII(@c);
  PRINT 'UNICODE: '   UNICODE(@c);
  SET @i  = 1;
END

You should be able to scan down and match the last sequence of characters you see in the broken print output. Then look for anything where the Raw: line is empty and the ASCII: line is anything other than typical (9, 10, 13, 32).

But I don't think this is the problem. I'll go back to an earlier comment where I suggested that the string itself is the problem. In the question, you mention @sql, but don't show how it's populated. I would bet that some string you're adding to that is getting truncated. Some things to look out for:

• Intermediate variables/parameters declared as varchar/nvarchar but with no length (which sometimes leads to silent truncation at 1 character, and sometimes 30):

    DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.table ';
    DECLARE @where nvarchar = N'WHERE some condition...';
    SET @sql  = @where;
    PRINT @sql;
  

  Output:

    SELECT * FROM dbo.table W
  

• Intermediate variables/parameters declared as varchar/nvarchar but too short (which leads to silent truncation at whatever the declaration is):

    DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.table ';
    DECLARE @where nvarchar(10) = N'WHERE some condition...';
    SET @sql  = @where;
    PRINT @sql;
  

  Output:

    SELECT * FROM dbo.table WHERE some
  

• Explicit CONCAT with NULL, which leads to silently dropping any NULL input):

    DECLARE @sql nvarchar(max) = N'SELECT * FROM dbo.table ';
    DECLARE @where nvarchar(32);
    DECLARE @orderby nvarchar(32) = N' ORDER BY col1';
    SET @sql = CONCAT(@sql, @where, @orderby);
    PRINT @sql;
  

  Output:

    SELECT * FROM dbo.table  ORDER BY col1
  

• Not using the N prefix when concatenating Unicode string literals > 4000 characters (example here):

    DECLARE @sql nvarchar(max) = '';
  
    SET @sql = @sql   '... literally 4001 characters ...';
  

  The output here (as shown in the example) will be truncated at 4,000 characters. However if you define your strings properly, this won't happen:

    DECLARE @sql nvarchar(max) = N'';
  
    SET @sql = @sql   N'... literally 4001 characters ...';
  

在過于復雜的動態 SQL 生成中很難發現這些東西，因此簡化并嘗試任何方式來劃分和征服最終字串中的主要組件絕不是一個壞主意。根據您嘗試的復制，我幾乎可以肯定地猜測這是“變數宣告太短”的癥狀。最安全的是確保動態 SQL 字串的每個輸入都應宣告為nvarchar(max); 除了受元資料約束的物體名稱之外，沒有真正好的理由使用其他任何東西。

標籤：

上一篇：如何將unpivot結果表與維度表連接起來？

下一篇：EFCore：與FirstOrDefaultAsync的區別