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

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?

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.