How to retrieve a result from a dynamic sql statem

• salenger

  SSC Journeyman

  Points: 75

  More actions

  April 30, 2003 at 2:46 am

  #101456

  Hi,

  If I have a query like this :

  set @sql='select count(*) from '+ @table

  execute (@sql)

  How can I retrieve the result of the count (or any function returning a single result) ? Please note that the table name is in a variable, thus the usage of dynamic sql.

  Thanks in advance,

  Greg

• Jeremy Kemp

  SSCertifiable

  Points: 6754

  More actions

  April 30, 2003 at 3:35 am

  #455773

  I've solved this by creating a temporary stored procedure:

  declare @sqlstring nvarchar(1000), @table varchar(25), @count int

  set @table = 'table'

  select @sqlstring = 'create procedure #count @count int output

  as

  select @count = count(*)

  from ' + @table

  /* print @sqlstring */

  exec sp_executesql @sqlstring

  exec #count @count output

  print @count

  drop procedure #count

  There are probably lots of reasons not to do this but I find it works well.

  Jeremy

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  April 30, 2003 at 10:43 am

  #455824

  declare @C int,@sql nvarchar(1000),@table varchar(20)

  set @table = 'tablename'

  set @sql = 'select @C=count(*) from '+@table

  exec sp_executesql @sql,N'@c int output',@c output

  select @C

  Far away is close at hand in the images of elsewhere.
  Anon.

• Jeremy Kemp

  SSCertifiable

  Points: 6754

  More actions

  May 1, 2003 at 1:34 am

  #455868

  David,

  That's much neater and probably a lot quicker - I'll being using that in future.

  Jeremy