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

Question

Hi,

This is for SS 2005.

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

It truncates the string @SQL1 at 4000.

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

Answer 1

Update: gbn's comment is right, and I was wrong. As MSDN points 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.

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)?

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.

Answer 2

Wait, I thought NVARCHAR(MAX) would allow you to store 2^31-1 bytes in the string. Once the 8000 bytes is reached (4000 characters as previously stated) it would simply perform like NTEXT and use a LOB with a pointer? It's late, what am I missing?

Answer 3

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

What is happening:

• The datatype is not yet nvarchar(max) until assignment to @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

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

One idea. The 2nd line concatenates nvarchar(max) with a constant = 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?

Answer 4

then why doesnt this work ?!?!?!

DECLARE @over4000 VARCHAR(MAX)

SET @over4000 = @somestringunder4000 + @somestringunder4000 + somestringunder4000

--total is over 4000

PRINT @over4000 --works 100%

DECLARE @nvarsql NVARCHAR(MAX)

SET @nvarsql = @over4000 --concatenates it !!!!!!!!!!!!!!!!!!!!!!