Variable datatype for creating dynamic query which exceeds 8000 charecters.

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  February 11, 2009 at 11:29 pm

  #201066

  Dear All,

  I have written procedure in which a dynamic query is created and exectued using sp_executesql.

  have declared the string variable as nVarchar(4000) for generating dynamic query.

  But the number of charecters may exceed 4000.

  So what should be the data type for string variable..?. Shall I use nText..?

  Thanks,

  swmsan.

• stephen.christie

  SSC Eights!

  Points: 895

  More actions

  February 12, 2009 at 12:11 am

  #942638

  Hi There,

  Why don't you use VARCHAR(MAX) this can take way more then the 4000 you are trying to use now.

  stay away from ntext can cause other issues down the line.

  Hope this helps

  Regards

  Stephen

• roy.neelanjana

  SSC Veteran

  Points: 293

  More actions

  February 12, 2009 at 12:15 am

  #942640

  You can declare a variable with varchar(8000) and use only exec statement.

  For example:

  DECLARE @sql varchar(8000)

  set @sql = 'Your String'

  exec (@sql)

  let me know whether it meets your requirement.

  Thanks

  NRoy

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  February 12, 2009 at 5:44 am

  #942746

  Follow Stephen's advice. Use the MAX data type and keep working with sp_executesql if you have to do dynamic queries.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Florent Rousseau

  Old Hand

  Points: 311

  More actions

  February 13, 2009 at 10:55 am

  #943807

  Hi,

  I also use some dynamically written queries and also ran into the limit of the maximum string length (VarChar(8000)).

  I solved it by cutting down the whole query in separate parts not supposed to get bigger than 8000 characters, then concatenating them :

  Declare @Query1 VarChar(8000)

  Declare @Query2 VarChar(8000)

  Declare @Query3 VarChar(8000)

  Set @Query1 = ...

  Set @Query2 = ...

  Set @Query3 = ...

  Execute(@Query1+@Query2+@Query3)

  Hope this can help you,

  Regards

  Florent

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  February 13, 2009 at 11:07 am

  #943823

  Florent Rousseau (2/13/2009)

  ---

  Hi,

  I also use some dynamically written queries and also ran into the limit of the maximum string length (VarChar(8000)).

  I solved it by cutting down the whole query in separate parts not supposed to get bigger than 8000 characters, then concatenating them :

  Declare @Query1 VarChar(8000)

  Declare @Query2 VarChar(8000)

  Declare @Query3 VarChar(8000)

  Set @Query1 = ...

  Set @Query2 = ...

  Set @Query3 = ...

  Execute(@Query1+@Query2+@Query3)

  Hope this can help you,

  Regards

  Florent

  And in SQL Server 2000, that is exactly a great solution for doing this. In SQL Server 2005/2008 though, you can take advantage of the MAX data type and you won't have to do the break down as you did.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  February 16, 2009 at 2:28 am

  #944371

  Hi,

  I sent the script using 'nText' already..Actualy the problem was that, user copy and paste some values from some files. It cause problem while saving...it throws error saying 'Invalid Charecter fiound..!!' .

  By the way, What kind of problems NTEXT causes..?

• Florent Rousseau

  Old Hand

  Points: 311

  More actions

  February 17, 2009 at 8:25 am

  #945236

  Grant Fritchey (2/13/2009)

  ---

  And in SQL Server 2000, that is exactly a great solution for doing this. In SQL Server 2005/2008 though, you can take advantage of the MAX data type and you won't have to do the break down as you did.

  I was indeed running this on SQL Server 2000, and kept doing the same on SQL Server 2005... ignoring the existence of this new MAX data type. I'll use it from now on and it will save me the hassle of breaking down the code in less than 8000 characters parts (which is always tricky when part of the code is dynamic and depends on user input). Thanks a lot!

• ramacm

  Grasshopper

  Points: 17

  More actions

  February 17, 2009 at 10:29 pm

  #945666

  Hi Florent

  It is working fine....

  Thanks

  Raam