string with more than 4000 characters

[ramesh_cr]

Hi,

How do I pass a string with more than 4000 characters to 'exec sp_executesql' statement?
Can I pass 2 variables like this 'exec sp_executesql @str1+@str2'.
Can you give some solution?

Thanks
Ramesh
ramesh_cr@yahoo.com

[Gregory64]

I think you can not do that because sp_executesql take NVARCHAR parameter and max size for NVARCHAR in MS SQL 2000 is 4000. If you doing dynamic SQL you may consider using EXEC() command. EXEC is taking varchar argument, so you limitation will be 8000 characters.

[buser]

BOL

Using EXECUTE with a Character String
Use the string concatenation operator (+) to create large strings for dynamic execution. Each string expression can be a mixture of Unicode and non-Unicode data types.

Although each [N] 'tsql_string' or @string_variable must be less than 8,000 bytes, the concatenation is performed logically in the SQL Server parser and never materializes in memory. For example, this statement never produces the expected 16,000 concatenated character string:

EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')

Statement(s) inside the EXECUTE statement are not compiled until the EXECUTE statement is executed.

[buser]

sp_executesql
Executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.

Syntax
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]

Arguments
[@stmt =] stmt

Is a Unicode string containing a Transact-SQL statement or batch. stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext. More complex Unicode expressions (such as concatenating two strings with the + operator) are not allowed. Character constants are not allowed. If a constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is legal, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory.