sp_executesql problem

• Loner

  SSC-Insane

  Points: 21279

  More actions

  September 8, 2006 at 1:05 pm

  #174151

  sp_executesql can only process 4000 characters, but my query exceeds more than 4000 characters. I don't have any parameter and I don't have any outout.  So I tried to do the following

  DECLARE @SQL1 NVARCHAR(4000), @SQL2 NVARCHAR(4000), @ReturnCode INT

  SET @SQL1 = N'Insert into TABLEA SELECT .....'

  SET @SQL2 = N'''A''  col1, ''B'' col2 FROM ftable1 WHERE...'

  EXEC('EXEC sp_executesql N''' + @SQL1 + @SQL2 + '''')

  I supposed to get a returncode from the query.  I don't know how to do it in this way.   Second it did not work, it kept saying something wrong in @SQL1.

  I know I can use EXEC (@SQL) that will allow me to use 8000 characters but I need to find out the result of the query if it runs fine.  

  Can someone help me either using EXEC(@SQL) and get the status of the query or how to get sp_executesql to work?

  Thanks

   

   

   

   

   

   

   

   

   

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 8, 2006 at 1:51 pm

  #659449

  If you are using SQL2005 use this

  declare

  @sql nvarchar(max)

  declare

  @ret int

  set

  @sql=N'Select * from sysobjects;'

  exec

  @ret=sp_executesql @sql

  select

  @ret

• Loner

  SSC-Insane

  Points: 21279

  More actions

  September 8, 2006 at 3:03 pm

  #659465

  Unfortunately I am using SQL Server 2000.

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 8, 2006 at 3:20 pm

  #659468

  I tried this and it worked.

  declare @sql1 nvarchar(4000),

   @sql2 nvarchar(4000)

  declare @ret int

  set @sql1=N'Select * from '

  set @sql2=N'sysobjects;'

  --exec @ret=sp_executesql @sql

  exec(@sql1 + @sql2)

  --select @ret

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 8, 2006 at 3:44 pm

  #659475

  This can allow upto 16000

  declare @sql1 varchar(8000),

   @sql2 varchar(8000),

  @ret int

  set @sql1=N'Select * from '

  set @sql2=N'sysobjects;'

  exec( @sql1 + @sql2)

  I think you need to capture return code, that is not possible with this 

• Loner

  SSC-Insane

  Points: 21279

  More actions

  September 9, 2006 at 1:28 pm

  #659507

  That is the reason why I need to use sp_executesql.