Question

using a local variable to build an sql-statement beyond datatype nvarchar(4000) and execute with sp_executesql

SQL, SQL Server

I build a sql-statement and put it in a local variable so i can execute it using sp_executesql, but a local variable cannot be a text or ntext data type, and a varchar data type cannot be used by sp_executesql. So the maximum lenght of the statement cannot be more than nvarchar(4000)...
is there any work around?

Pretty much no. I ran into this same problem with some dynamic SQL that we had, and I had to remove some of the code from the dynamic SQL and pass it's results into the sp_executesql via the parameter.

Do as much of the logic as you can outside of the dynamic SQL so that only the final select statement is dynamic.