max size of sql statement parameter

• Vera-428803

  SSCrazy

  Points: 2439

  More actions

  October 22, 2008 at 5:25 am

  #96757

  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.

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  October 22, 2008 at 5:59 am

  #888048

  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

• Glen

  SSCertifiable

  Points: 6737

  More actions

  October 22, 2008 at 6:20 am

  #888061

  Did you ever tried Varchar(MAX).

  In SQL server 2000?

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  October 22, 2008 at 6:41 am

  #888072

  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

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  October 22, 2008 at 6:50 am

  #888074

  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

• Vera-428803

  SSCrazy

  Points: 2439

  More actions

  October 28, 2008 at 3:15 am

  #890454

  Concatenating the string works.

  Thanks a lot.