Dynamic SQL

• Gagandeep Singh

  SSCommitted

  Points: 1513

  More actions

  July 20, 2005 at 10:40 am

  #66737

   Is it possible to replace this:

  DECLARE @sql varchar(255)

  DECLARE @tablename varchar(255)

  DECLARE @rcount int

  set @sql = 'Select COUNT(*) as COUNT into ##TEMPCOUNT from ' + @tablename

  EXECUTE sp_executesql @SQL

  SELECT @rcount = [COUNT] FROM ##TEMPCOUNT

  with:

  set @sql = 'Select ' + @rcount + ' = COUNT(*) as COUNT from ' + @tablename

  EXECUTE sp_executesql @SQL

  and eliminate the temporary table creation?

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  July 20, 2005 at 11:08 am

  #575883

  Not possible. The variable @rcount is out of scope in Dynamic SQL.

  May be steev's answers in this helps

  http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=202194#bm202408

  Regards,
  gova

• Gagandeep Singh

  SSCommitted

  Points: 1513

  More actions

  July 20, 2005 at 1:27 pm

  #575908

  Thanks a lot Govin.

   

  Steve's answer helped.

  This will do:

  set @sql = 'select @rcount = Count(*) from ' + @tablename

  exec sp_executesql @sql, N'@rcount int OUTPUT', @rcount OUTPUT