VarChar Length>8000

[arpan]

I want to use a variable in a stored procedure whose length exceeds 8000. If I use

DECLARE
@sql varchar(12000)

an error is generated saying that the max length is 8000. So how do I declare & then use a variable whose length is greater than 8000?

Thanks,

Arpan

 

[lemosley01]

See the text an ntext data types in SQL Books Online.

 

[tlbroadbent]

You cannot declare a variable longer than 8000 characters. You cannot declare local variables of type text or ntext. You can however concantenate variables.

Declare @sql1 varchar(8000), @sql2 varchar(8000)

Exec(@sql1 + @sql2) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[lemosley01]

Thanks Terry. I've read that before but glossed right over the fact the BOL says they cannot be used as variables.

Guess the day hasn't been a waste.

Lawrence M.

 

[lemosley01]

Terry,
Out of curiosity, is there a maximum statement length for select, inserts, etc?

 

[tlbroadbent]

According to SQL Setver BOL, the maximum "length of a string containing SQL statements (batch size) is 65,536 * Network packet size" where the network packet size is typically 4k. I've never come close to creating a query or procedure that was that long. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.