Post reply

Declaring local variable larger than 8000 characters

  • January 23, 2004 at 9:29 am

    #84366

    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

    #491432

    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

    #491448

    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

    #491450

    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

    #491564

    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