title: Loop with dynamic table name and where clause stored procedure
body: <p>I am trying to update records and insert their audits to audit table.</p>
  
  <p>For this purpose stored procedure waiting for above variables.</p>
  
  <p><code>@m_obj_id INT,
   @m_obj_code NVARCHAR(250),
   @m_f_code NVARCHAR(250),
   @m_nv NVARCHAR(4000),
   @m_last_mod_by INTEGER,
   @table_name SYSNAME,
   --@where_clause NVARCHAR(4000)</code></p>
  
  <p>Stored procedure formatting these variables as;</p>
  
  <pre><code>UPDATE @table_name SET @m_f_code=@m_nv WHERE id=@m_obj_id
  </code></pre>
  
  <p>And at last part inserting into audit.</p>
  
  <p>I can use it with doing <code>SELECT CONCAT</code> and copying all the rows then execute.</p>
  
  <p>But my goal is here not expecting <code>@m_obj_id</code> from user and replace it <code>@where_clause</code>. And use this <code>@where_clause</code> to get ids inside.</p>
  
  <p>So far I tried;</p>
  
  <pre><code>    DECLARE @Sql NVARCHAR(MAX)
      DECLARE @RecordId int = 0
      BEGIN
      SET @SQL = N'
        SELECT @RecordId = MIN(id)
        FROM ' + @table_name + '
        WHERE id &gt; @RecordId AND (' + @where_clause + ')
        IF @RecordId IS NULL BREAK
        SET @m_obj_id = @RecordId'
  Exec sp_executesql @sql
  </code></pre>
  
  <p>But couldnt get far with it.</p>
  
  <p>Then I tried something like;</p>
  
  <pre><code>DECLARE @RowsToProcess  int
  DECLARE @CurrentRow     int
  DECLARE @SelectCol1     int
  
  DECLARE @sql NVARCHAR(MAX)
  SET @sql = N'
  DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int )  
  INSERT into @table1 (col1) SELECT id FROM ' + @table_name + ' Where ' + @where_clause + '
  SET @RowsToProcess=@@ROWCOUNT'
  EXEC sp_executesql @sql,
  N'@RowsToProcess INT OUTPUT', @RowsToProcess OUTPUT
  
  SET @CurrentRow=0
  WHILE @CurrentRow&lt;@RowsToProcess
  BEGIN
      SET @CurrentRow=@CurrentRow+1
          DECLARE @sql2 NVARCHAR(MAX)
          SET @sql2 = N'
          SET @m_obj_id =
          (SELECT col1
          FROM @table1
          WHERE RowID=@CurrentRow)'
          EXEC sp_executesql @sql2
  </code></pre>
  
  <p>But still no luck.</p>
  
  <p>Can I achieve this any how? I am trying to do this for more than it should be.
  Thanks all.</p>