How to execute a very long dynamic SQL query?

Question

I remember that the day I would do all wack nvarchar(4000)vars, check their length as they grew, turn them off when they filled, and then combine all the clutter to call exec. I was wondering if there is an easier way to do this.

Thank!

Edit:

Sample code shows that I am giving a case argument

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

Answer 1

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

[ @statement= ] statement

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

Operator

Unicode Unicode. Unicode, + , . . Unicode, N. , Unicode N'sp_who ' , "sp_who" - . . 64- , 2 , NVARCHAR ().

Answer 2

EXEC (@YourSQL)

sp_exectesql, SQL .

=)