title: How to create temp table from a dynamic query?
body: <p>I want to create dynamic query for paging [<strong>Main Motive</strong>], here is my code:</p>
  
  <pre><code>Alter proc proc_GetData
  (
      @TableName varchar(500)='tblPropertyType',
      @PrimaryKey varchar(500)='Id',
      @Columns varchar(max)='PropertyType',
      @WhereCondition varchar(max)='',
      @RecsPerPage int =3,
      @Page int=1,
      @SortColumn varchar(500)='Id',
      @SortDirection Varchar(56)='asc'
  )
  as
  DECLARE @SQL VARCHAR(MAX)
  DECLARE @DSQL VARCHAR(MAX)
  DECLARE @FirstRec int, @LastRec int
  SET @FirstRec = (@Page - 1) * @RecsPerPage
  SET @LastRec = (@Page * @RecsPerPage + 1)
  select @FirstRec
  select @LastRec
  SET @SQL='
  SELECT
  ROW_NUMBER() OVER (ORDER BY ' + @SortColumn+ ' '+ @SortDirection +') RowNum,'+ @PrimaryKey +' , ' +@Columns + ' from ' +@TableName+ ' Where  1=1 '+
  @WhereCondition
  </code></pre>
  
  <p>What I want to do is:</p>
  
  <ul>
  <li>First: inert all the records in a temp table from the above query.</li>
  <li>Second: <code>SELECT * FROM @TEMPResult WHERE RowNum &gt; @FirstRec AND RowNum &lt; @LastRec</code>.</li>
  </ul>
  
  <p>Please help me</p>