SQL 对于 Nvarchar(Max) 我只能在 TSQL 中获得 4000 个字符？

For Nvarchar(Max) I am only getting 4000 characters in TSQL?

提问by Malcolm

This is for SS 2005.

这是 SS 2005。

Why I am i only getting 4000 characters and not 8000?

为什么我只得到 4000 个字符而不是 8000 个？

It truncates the string @SQL1 at 4000.

它在 4000 处截断字符串 @SQL1。

ALTER PROCEDURE sp_AlloctionReport(
    @where NVARCHAR(1000),
    @alldate NVARCHAR(200),
    @alldateprevweek NVARCHAR(200))
AS
    DECLARE @SQL1 NVARCHAR(Max)

    SET @SQL1 = 'SELECT DISTINCT VenueInfo.VenueID, VenueInfo.VenueName, VenuePanels.PanelID, 
    VenueInfo.CompanyName, VenuePanels.ProductCode, VenuePanels.MF, VenueInfo.Address1, 
    VenueInfo.Address2, '' As AllocationDate, '' As AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, 
    VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, 
    VenueCategories.Category, VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, 
    [VenueCategories].[Category] + '' Allocations'' AS ReportHeader, 
    ljs.AbbreviationCode AS PrevWeekCampaign
    FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) 
    INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) 
    LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate
                    FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID
                    WHERE ' + @alldateprevweek + ') ljs
                ON VenuePanels.PanelID = ljs.PanelID) 
    INNER JOIN (SELECT VenueInfo.VenueID, VenuePanels.PanelID, VenueInfo.VenueName, VenueInfo.CompanyName, VenuePanels.ProductCode, 
                VenuePanels.MF, VenueInfo.Address1, VenueInfo.Address2, CampaignAllocations.AllocationDate, 
                CampaignProductions.AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, 
                VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, VenueCategories.Category, 
                VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, [Category] + '' Allocations'' AS ReportHeader, 
                ljs2.AbbreviationCode AS PrevWeekCampaign
                FROM ((((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) 
                INNER JOIN CampaignAllocations ON VenuePanels.PanelID = CampaignAllocations.PanelID) 
                INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID = CampaignProductions.CampaignID) 
                INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) 
                LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate
                                FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID
                                WHERE ' + @alldateprevweek + ') ljs2
                            ON VenuePanels.PanelID = ljs2.PanelID
                WHERE ' + @alldate + ' AND ' + @where + ') ljs3
                ON VenueInfo.VenueID = ljs3.VenueID
    WHERE (((VenuePanels.PanelID)<>ljs3.[PanelID] And 
        (VenuePanels.PanelID) Not In (SELECT PanelID FROM CampaignAllocations WHERE ' + @alldateprevweek + ')) 
        AND ' + @where + ')
    UNION ALL
     SELECT VenueInfo.VenueID, VenueInfo.VenueName, VenuePanels.PanelID, VenueInfo.CompanyName, VenuePanels.ProductCode, 
    VenuePanels.MF, VenueInfo.Address1, VenueInfo.Address2, CampaignAllocations.AllocationDate, 
    CampaignProductions.AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, 
    VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, VenueCategories.Category, 
    VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, [Category] + '' Allocations'' AS ReportHeader, 
    ljs.AbbreviationCode AS PrevWeekCampaign
    FROM ((((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) 
    INNER JOIN CampaignAllocations ON VenuePanels.PanelID = CampaignAllocations.PanelID) 
    INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID = CampaignProductions.CampaignID) 
    INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) 
    LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate
                    FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID
                    WHERE ' + @alldateprevweek + ') ljs
                ON VenuePanels.PanelID = ljs.PanelID
    WHERE ' + @alldate + ' AND ' + @where

    Select @SQL1

回答by gbn

You have declared this as nvarchar(max) which allows 2GB of data so it will store 2GB.

您已将其声明为 nvarchar(max)，它允许 2GB 的数据，因此它将存储 2GB。

What is happening:

怎么了：

• The datatype is not yet nvarchar(max) until assignmentto @sql1
• Before that, it's a collection of strings, each less than 4000 (constants)
• You are concatenating short constants with short variables (short = < 4000)
• So you have 4000 characters put into @sql1

• 在分配给@sql1之前，数据类型还不是 nvarchar(max)
• 在此之前，它是一个字符串的集合，每个小于 4000（常量）
• 您将短常量与短变量连接在一起（短 = < 4000）
• 所以你有 4000 个字符放入@sql1

So, you have make sure you have nvarchar(max) on the right hand side.

因此，您必须确保右侧有 nvarchar(max)。

One idea. The 2nd line concatenates nvarchar(max) with a constant = nvarchar(max)

一个想法。第二行将 nvarchar(max) 与一个常量 = nvarchar(max) 连接起来

SET @SQL1 = ''
SET @SQL1 = @SQL1 + 'SELECT DISTINCT Venue...
   ....

It's no different to the integer division that happens in every langauge.

它与发生在每种语言中的整数除法没有什么不同。

declare @myvar float
set @myvar = 1/2 --gives zero because it's integer on the right

Operator precedence (infers datatype precedence) is always "assignment" last... why should unicode strings in SQL Server be any different?

运算符优先级（推断数据类型优先级）总是“赋值”最后......为什么 SQL Server 中的 unicode 字符串应该有所不同？

回答by Daniel Pryden

Update: gbn's answeris right, and I was wrong. As MSDNpoints out, nvarchar(max) supports up to 2^31-1 bytes of data, stored as UCS-2 (2 bytes per character, plus 2 for BOM). Your problem seems to be with string concatenation, not data type limits.

更新：gbn 的回答是对的，我错了。正如MSDN指出的那样，nvarchar(max) 最多支持 2^31-1 字节的数据，存储为 UCS-2（每个字符 2 个字节，加上 2 个用于 BOM）。您的问题似乎与字符串连接有关，而不是数据类型限制。

That said, if you're using it to build a SQL string, why not use VARCHAR? Do you have field names that aren't representable by the database's native character set (usually Latin-1)?

也就是说，如果您使用它来构建 SQL 字符串，为什么不使用 VARCHAR？您是否有无法由数据库的本机字符集（通常是 Latin-1）表示的字段名称？

Finally -- you could simplify your entire problem by just not using dynamic SQL in your stored procedure. Create some table-valued functions that take your where-clause strings and return tables, and then just JOIN them in your procedure. As a bonus it will almost certainly be much faster, since at very least the database will be able to cache the SP body as a prepared statement.

最后——您可以通过在存储过程中不使用动态 SQL 来简化整个问题。创建一些表值函数，它们接受您的 where 子句字符串并返回表，然后在您的过程中加入它们。作为奖励，它几乎肯定会快得多，因为至少数据库将能够将 SP 主体缓存为准备好的语句。

回答by Mahdi

i resolve problem just include N character before every string and problem solved for example

我解决的问题只是在每个字符串和问题解决之前包含 N 个字符

declare @sql nvarchar(max) = '' + @Where + 'SomeThing';

must be

必须是

declare @sql nvarchar(max) = N'' + @Where + N'SomeThing';

if you set string to empty also must set N''

如果您将字符串设置为空也必须设置 N''

if @where is null
set @where = N''

:-) simple answer

:-) 简单的答案