maximum length reached using varchar?

[bunmiA]

I am running a dynamic sql query which I have declared as varchar(8000). The query is longer than 8000 characters long. Does anyone have any ideas of how I can code this.

Thanks
Bunmi

 

[Pattycake245]

I'm thinking you'll have to declare a second string variable and break up the query, then execute them together at the end. Something like:

declare @SetString varchar(8000), @SetString2 varchar(8000)
select @SetString = N'Select top 2 * from table'
select @SetString2 = N' where fname=''Bob'''
print @SetString + @SetString2
exec('sp_executesql N''''' + @SetString + '' + @SetString2 + '')

You may need to do this before you execute the two strings:

set @SetString = replace(@SetString,'''','''''')
set @SetString = replace(@SetString2,'''','''''')

This is to ensure the quotes inside your qureies are accounted for properly as the exec statement will mess all the quotes up in your queries.

Fill in whatever query you like in the strings to test this out. Let me know if this works for you.

Tim