sp_executesql with sql > 4000

• Nils Gruson

  SSC Rookie

  Points: 43

  More actions

  October 14, 2005 at 6:45 am

  #88186

  I have a stored procedure where a SQL string is built depending on a bunch of input parameters. When the SQL is built, it is executed with sp_executesql. However, now I have a situation that the SQL becomes very large. I made the variable @SqlString varchar(8000) instead of nvarchar(4000), but it still doesn't work. I'm suspecting that sp_executesql can't handle a string larger than 4000. Can anyone confirm this and help me with a solution for this problem?

  Regards,

  Nils

• rsharma

  SSCarpal Tunnel

  Points: 4865

  More actions

  October 14, 2005 at 7:17 am

  #597336

  You can use EXEC instead of sp_executesql in such a scenario:

  declare @col1 varchar(8000), @col2 varchar(8000), @col3 varchar(8000)

  set @col1 = 'select * from '

  set @col2 = 'sysobjects where '

  set @col3 = 'xtype = ''u'''

  exec (@col1 + @col2 + @col3)