Exceeding 4000 Character Parameter Limit.

Question

Post reply

Exceeding 4000 Character Parameter Limit.

homebrew01

SSC Guru

Points: 55548
More actions
October 6, 2009 at 9:56 am

#156291

A developer has a situation in which they want to put a sql statement greater than 4000 characters in to a parameter to be executed. Is there an elegant (or not) workaround for this ?
Edit: I searched a bit and found the suggestion to do this, but haven't tried it yet. Will it work ?
declare @code_1 varchar(4000)
declare @code_2 varchar(4000)
select @code_1 = 'Very long code ....'
select @code_2 = 'The rest of the code...'
exec (@code_1 + ' ' + @code_2)
Edit 2 .... Above code seems to work

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

Just FYI, there was a discussion around similar problem I guess,

http://www.sqlservercentral.com/Forums/Topic795852-1291-1.aspx

---------------------------------------------------------------------------------

sanoj_av2001 SSChasing Mays Points: 601 More actions · Answer 2

It will work...

declare @code_1 varchar(4000)

declare @code_2 varchar(4000)

declare @code_3 varchar(100)

select @code_1 = replicate('A',4000)

select @code_2 = replicate('B',4000)

Select @code_3='Select '

Exec (@code_3+ ''''+ @code_1 + '''' + ''''+ @code_2 + '''')

..

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

What about using VARCHAR(MAX) and use sp_executesql. According to BOL the @stmt parameter in sp_executesql is only limited by available memory.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

Grant Fritchey (10/7/2009)
What about using VARCHAR(MAX) and use sp_executesql. According to BOL the @stmt parameter in sp_executesql is only limited by available memory.

Yeah, thats true! As per BOL,

varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying.

http://msdn.microsoft.com/en-us/library/ms176089(SQL.90).aspx

---------------------------------------------------------------------------------