title: Need to use Update statement with dynamic sql in sqlserver 2008
body: <p>I have a target table <code>dbo.val</code> with three columns: <code>table_schema</code>, <code>table_name</code> and <code>row_count</code>, with the schema and name already populated and respective row counts set to zero for a particular database.<br>
  I need row counts for each table name, but can't use system tables for row count as they might give inaccurate data at times.</p>
  
  <p>I used a cursor to get table and schema name from <code>INFORMATION_SCHEMA.TABLES</code> to get populated in <code>@tb_schema</code> and <code>@tb_table</code> variables and used a third variable <code>@c_name</code> to club both</p>
  
  <pre><code>set @c_name = @tb_schema + '.' + @tb_name; 
  </code></pre>
  
  <p>I need an update stament to take dynamic count from <code>@c_name</code> and populate the <code>row_count</code> column of <code>dbo.val</code> which can later put in a loop<br>
       This is what I tried but it didnt work:</p>
  
  <pre><code> update dbo.val set row_count = Exec ('select COUNT(*) from ' + @full_name)
   where table_schema = @tb_schema and table_name =@tb_name  ;
  </code></pre>
  
  <p>I also tried the below, which didnt work as well</p>
  
  <pre><code>declare @catch numeric (20)
  select @catch = Exec ('select COUNT(*) from ' + @full_name) ;
  update dbo.validate set cnt = @catch
  where tb_schema = @tb_schema and tb_name =@tb_name  ;
  </code></pre>
  
  <p>Can anyone help me out, as to how to update the row count of my target table?</p>