How do I execute a very long dynamic sql statement?

Question

I remember back in the day I would make a whole wack of nvarchar(4000) vars, check the length of them as they grew, switch them out as they filled up and then concatenate the whole mess together for the exec call. I was wondering if there was an easier way of doing it.

Thanks!

Edit:

Code Sample, shows me screwing up the case statement

DECLARE @sql NVARCHAR(MAX)
SELECT @sql = CAST(N'SELECT ' AS NVARCHAR(MAX))

DECLARE @Index INT
SELECT @Index = 0

WHILE (@Index < 1000)
BEGIN
 SELECT @sql = CAST(@sql AS NVARCHAR(MAX)) + CAST(N'          ' AS NVARCHAR(MAX)) + CAST( CASE @Index WHEN 1 THEN N' ' END AS NVARCHAR(MAX))
 SELECT @Index = @Index + 1
END
SELECT @sql = CAST(@sql AS NVARCHAR(MAX)) + CAST(1 AS NVARCHAR(MAX))

SELECT LEN(@sql)
EXECUTE sp_executesql @sql

Pre

I might not understand the question, but why not use nvarchar(MAX) variable(s) - (SQL 2005 and up)? – Mandler

What's the question? Dynamic SQL means string concatenation... – Donatelli

@Mandler - add your comment as an answer. Spot on – Hermaphrodite

@Hermaphrodite - someone beat me to it :) – Mandler

Answer 1

sp_executesql accepts a parameter of type NVARCHAR(MAX) which can grow up to 2GB. There is no need for any gimmick, since the NVARCHAR(MAX) type supports all the string operations (concatenation, replacing etc):

[ @statement= ] statement

Is a Unicode string that contains a Transact-SQL statement or batch.

statement must be either a Unicode constant or a Unicode variable. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Character constants are not allowed. If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

Statistics

Lets hope I get to deploy this application on SQL Server 2005 + – Pre

Just be mindful of some of the string functions: DECLARE @x nvarchar(max) SET @x = REPLICATE('a',10000) SELECT LEN(@x) -- 8000 -- needs to be SET @x = REPLICATE(CAST('a' AS nvarchar(max)),10000) – Sialoid

I still can't get my NVARCHAR(MAX) var above 4k characters. :( – Pre

Argh! A case statement was messing me up! Thanks again Remus. – Pre

64-bit is limited to 2 GB? I thought that was the limit for 32-bit. – Domett

Answer 2

EXEC (@YourSQL) OR sp_exectesql if you wish to continue to build onto your dynamic SQL and execute.

Handpick