sp_executesql giving error inside stored procedure

• shatrughna

  Ten Centuries

  Points: 1037

  More actions

  April 15, 2011 at 1:01 am

  #249454

  Hi All,

  I have created a dynamic statement using sp_executesql but i am getting below error on execution.

  Error:

  Could not find stored procedure 'SELECT @TDATA=COUNT(*) FROM TBL_Q6310'.

  Statement inside stored procedure

  SET @SQLString = N'SELECT @TDATA=COUNT(*) FROM '+@QName

  EXEC dbo.sp_executesql

  @query = @SQLString,

  @params = N'@TDATA INT OUTPUT',

  @TDATA = @TDATA OUTPUT

  Please guide me how can i resolve this issue.

  Thanks!

  Shatrughna

  Shatrughna

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  April 15, 2011 at 6:38 am

  #1312077

  Try this

  DECLARE @SQLString nvarchar(500);

  DECLARE @ParmDefinition nvarchar(500);

  DECLARE @TDATA varchar(30);

  declare @QName varchar(50)='TBL_Q6310'

  SET @SQLString = N'SELECT @TDATAOUT = Count(*) FROM '+@QName

  SET @ParmDefinition = N' @TDATAOUT varchar(30) OUTPUT';

  print @SQLString

  print @ParmDefinition

  EXECUTE sp_executesql @SQLString, @ParmDefinition, @TDATAOUT=@TDATA OUTPUT;

  SELECT @TDATA;

  Thanks
  Parthi

• shatrughna

  Ten Centuries

  Points: 1037

  More actions

  April 15, 2011 at 6:57 am

  #1312092

  Thanks Parthi for your valuable effort.

  I got the solution.

  Thanks!

  Shatrughna

  Shatrughna