What is the limitation in the length of an SqlCommand query

Here is a thought:

SQLServer 2000's VARCHAR allows up to 8000 characters, so this might work:

PSeudoCode:

SQLCommand command = new SqlCommand("exec sp_executeSQL @CMD");command.Parameters.Add(new SqlParameter("@CMD",YourDynamicSQL, VARCHAR);

SqlServer 2000 has a 4000 character query limit for adhoc queries.

Can you abstract this into a stored procedure?

a must read for dynamic queries... The Curse and Blessings of Dynamic SQL, I highly recommend you read it. Might not help you this time but it'll definitely help you in the future..

A quote from the article, just in case.

sp_executesql and Long SQL Strings in SQL 2000

There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005 and later, you should use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql when your query string exceeds this limit to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():

DECLARE @sql1 nvarchar(4000), @sql2 nvarchar(4000), @state char(2) SELECT @state = 'CA' SELECT @sql1 = N'SELECT COUNT(*)' SELECT @sql2 = N'FROM dbo.authors WHERE state = @state' EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + ''', N''@state char(2)'', @state = ''' + @state + '''')

This works, because the @stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.

You can even use output parameters by using INSERT-EXEC, as in this example:

CREATE TABLE #result (cnt int NOT NULL) DECLARE @sql1 nvarchar(4000), @sql2 nvarchar(4000), @state char(2), @mycnt int SELECT @state = 'CA' SELECT @sql1 = N'SELECT @cnt = COUNT(*)' SELECT @sql2 = N'FROM dbo.authors WHERE state = @state' INSERT #result (cnt) EXEC('DECLARE @cnt int EXEC sp_executesql N''' + @sql1 + @sql2 + ''', N''@state char(2), @cnt int OUTPUT'', @state = ''' + @state + ''', @cnt = @cnt OUTPUT SELECT @cnt') SELECT @mycnt = cnt FROM #result

You have my understanding if you think this is too messy to be worth it.