Problem with triggering SP_EXECUTESQL with a @stmt-Parameter > 4000 characters ...
Posted on 2006-06-20
My platform is Windows 2000 Server with a SQL Server 2000 running.
I hav a Stored Procedure on my SQL-Database which builds and executes a specific query dynamically using 'exec SP_EXECUTESQL @MySQLStatement' .
Therefor I declared a local var @SQL of type nvarchar(4000).
This variable is filled up with a large SQL-Statement (depending on some input-parameters of the Stored Procedure).
Now the problem is that in some cases my built SQL-Statement is getting larger than 4000 characters. And then I get an error of course because my SQL-Statement is not complete.
Is there a way to work around this problem and make use of another type of variable or do some conversion or ...?
I found something about ntext-variables, but I can't declare them as local vars in my Stored Procedure.
Anyone having a solution for my problem ?
Thanks in advance,