How do I execute a very long dynamic sql statement on SQL Server 2000?

Question

Hello,

I generate an sql string select statement that is around 25k characters long. Unfortunately the production server is SQL 2000.
Is my only option to break the string up into 4k nvarchars?

--ex.
DECLARE @s1 NVARCHAR(4000)
DECLARE @s2 NVARCHAR(4000)
DECLARE @s3 NVARCHAR(4000)
DECLARE @s4 NVARCHAR(4000)
DECLARE @s5 NVARCHAR(4000)
DECLARE @s6 NVARCHAR(4000)
DECLARE @s7 NVARCHAR(4000)

--fill nvarchars

EXEC @s1 + @s2 + @s3 + @s4 + @s5 + @s6 + @s7

Answer 1

Try this:

CREATE PROCEDURE DynamicSQLExec
   @SQL ntext
AS
EXEC (@SQL)

You can't declare a text local variable, but you can have one be a parameter for a stored procedure.