max size of sql statement parameter

  • SQL server 2000, stored procedure with EXEC sp_executesql @SQL.

    @SQL is declared as nvarchar(4000) which seems to be the maximum size.

    However, sometimes my parameter including the statement exceeds this size.

    How can I increase this size?

    Or can I split my statement and concatenate it in the EXEC sp_executesql @SQL?

    Any help would be appreciated.

    Thanks.

  • Vera (10/22/2008)


    SQL server 2000, stored procedure with EXEC sp_executesql @SQL.

    @SQL is declared as nvarchar(4000) which seems to be the maximum size.

    However, sometimes my parameter including the statement exceeds this size.

    How can I increase this size?

    Or can I split my statement and concatenate it in the EXEC sp_executesql @SQL?

    Any help would be appreciated.

    Thanks.

    Did you ever tried Varchar(MAX).

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • Did you ever tried Varchar(MAX).

    In SQL server 2000?

  • Glen (10/22/2008)


    Did you ever tried Varchar(MAX).

    In SQL server 2000?

    oh im sorry for that

    but in sql server 2000

    there is varchar(8000)

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • varchar(max) is only available as from SQL2005 !

    However, you can concatenate variables to execute with exec.

    Here's a little example:.

    EXEC (@SQLQry + @SQLQryp1 + @SQLQryp2 + @SQLQryp3 + @SQLQrypx)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Concatenating the string works.

    Thanks a lot.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply