January 23, 2004 at 9:29 am
Hello:
I'm building a SQL dynamically that is larger than 8000 characters. Varchar does not let me store more than 8000 characters. Can anybody suggest an idea how accomplish this in a stored procedure.
Thanks
Nix
January 23, 2004 at 10:43 am
From BOL:
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')
--Jonathan
January 23, 2004 at 12:33 pm
Thank you for that solution and your time. However, the problem is, a stored procedure is returning this query in an output variable. And the output variable has problems returning value over 8000 characters.
January 23, 2004 at 12:38 pm
Use multiple output variables, each with a piece of the query being built, and then concatenate those variables in the EXEC() in the calling proc.
--Jonathan
January 26, 2004 at 1:27 am
If I understand correct, you have a sp that returns a string that is to be executed dynamically?
That string is longer than 8000 characters?
There are only but a few tasks (sysadmin tasks) where dynamic sql is an acceptable solution
I don't know your business model and your data model, but I suspect that your approach might not be the best around. Don't get me wrong, I'm not going to argue against what you are trying (after all, it's your responsibility), but this might be interesting for you.
http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply