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

Question

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

+1, I have solved this using place holders for the variables, and then replace them. — gotqn, Nov 01 '13 at 08:44
I think this is a better answer to this question: http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits — IHTS, Mar 18 '14 at 18:48
@IHTS I agree - I think it is better if a user just get's directed to the other QA. — whytheq, Oct 23 '14 at 14:56

gbn · Accepted Answer · 2009-09-03T05:48:38.563

49

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?

edited Sep 03 '09 at 05:48

answered Sep 03 '09 at 05:42

gbn

394,550
75
549
647

1

+1. I would like to add, you can have a single string constant of more than 8000 varchar(max) or 4000 nvarchar(max). But if they are less than 8000/4000 they will not be of the max variety. – Shannon Severance Sep 03 '09 at 06:38
+1 But I am founding this very foolish. Let's say OK for MS, there is no sense to use/allocated (MAX) size, in case you do not need it, but when you need it (for example the case above, which is my case too) it is stupid for me to cut the string. – gotqn Nov 01 '13 at 08:43

score 4 · Answer 2 · edited Jan 08 '19 at 16:38

4

Update: gbn's answer 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.

edited Jan 08 '19 at 16:38

Robert Harvey

168,684
43
314
475

answered Sep 03 '09 at 04:19

Daniel Pryden

54,536
12
88
131

This does not answer the question: @sql1 is declared as nvarchar(max) which allow 2GB. – gbn Sep 03 '09 at 05:47
@gbn: You're right, and I've edited my answer. I still think using table-valued functions is the better solution here, though. – Daniel Pryden Sep 03 '09 at 05:51
@Daniel: yes, or varchar, or real SQL as you mentioned. – gbn Sep 03 '09 at 05:57
does not work for me unfortunately. The claim is thus that just be making the nvarchars equal to nvarchar(max) , it will work? – Peter PitLock Nov 19 '12 at 10:45

score 0 · Answer 3 · answered Sep 03 '15 at 06:04

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

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''

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

:-) simple answer

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

3 Answers3

Linked

Related