Number os rows in an dynamic query

• rechousa

  SSC Enthusiast

  Points: 165

  More actions

  September 2, 2002 at 9:37 am

  #173331

  Hi !

  I've a dynamic query in a variable, like this :

  DECLARE @SQLString NVARCHAR(2000)

  DECLARE @TABLE VARCHAR(2000)

  SET @SQLString = 'SELECT COUNT(*) FROM ' + @TABLE

  EXEC(@SQLSTRING)

  I would like to return the result, but I can't...

  I've tried :

  SET @SQLString = 'set @num_rows = (SELECT COUNT(*) FROM ' + @TABLE + ')'

  EXEC SP_EXECUTESQL @SQLSTRING, N'@num_rows int', @num_rows=0

  but, no sucess...

  Thanks in advance,

  Pedro Martins

  Rechousa,

  V.N.Gaia

  Portugal

  
  Rechousa,
  V.N.Gaia
  Portugal

• NPeeters

  SSChampion

  Points: 12358

  More actions

  September 2, 2002 at 10:15 am

  #435803

  You can't return a result from a dynamic query. The easiest solution to your problem is inserting the result in a temporary table.

  CREATE TABLE #temp(recordcount int)

  SET @SQLString = 'INSERT INTO #temp (recordcount) (SELECT COUNT(*) FROM ' + @TABLE + ')'

  EXEC(@SQLSTRING)

  select @ret_value = recordcount from #temp

  drop table #temp

• Prakash Heda

  SSCertifiable

  Points: 5539

  More actions

  September 2, 2002 at 11:31 pm

  #435812

  This is how a value can be returned from dynamic statement....

  declare @SQL_STMT NVARCHAR(4000)

  DECLARE @CHECK_CONSTRAINT_VALUE VARCHAR(200)

  SET @SQL_STMT = '

  SELECT @CHECK_CONSTRAINT_VALUE = count(*) from dim_gender'

  EXEC SP_EXECUTESQL @SQL_STMT, N'@CHECK_CONSTRAINT_VALUE VARCHAR(200) OUTPUT', @CHECK_CONSTRAINT_VALUE OUTPUT

  print @CHECK_CONSTRAINT_VALUE

  Cheers,

  Prakash

  Prakash Heda
  Lead DBA Team - www.sqlfeatures.com
  Video sessions on Performance Tuning and SQL 2012 HA

• Antares686

  SSC Guru

  Points: 125444

  More actions

  September 3, 2002 at 3:57 am

  #435826

  Notice the difference here

  EXEC SP_EXECUTESQL @SQLSTRING, N'@num_rows int', @num_rows=0

  and

  EXEC SP_EXECUTESQL @SQL_STMT, N'@CHECK_CONSTRAINT_VALUE VARCHAR(200) OUTPUT', @CHECK_CONSTRAINT_VALUE OUTPUT

  Your varibale is not designated for OUTPUT.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)