What datatype can be used instead of varchar for local variables?

[Ankor]

This code runs:

USE pubs
DECLARE @s VARCHAR(50)
SET @s = 'SELECT * FROM publishers'
EXEC (@s)

What if @s is greater than VARCHAR(8000)? If I replace VARCHAR with TEXT, I get an error message: "The text, ntext, and image data types are invalid for local variables".

 

[vongrunt]

Actually limit is 4000 characters.

Then you split large string into smaller <4k units and use + (e.g. EXEC( @s + @s1 ).

Or stop using dynamic SQL

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Ankor]

SQL 2000 limit is 8000 characters (BOL Data Types (Level 2)). The second suggestion might work. Thanks!