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

Accepted Answer

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?

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

Solution

Problem

Related problems