title: Pagination in stored procedure sql server
body: <p>I have implemented pagination for stored procedure which displays 2 table records one after the other. The pagination logic works perfectly for the first table records but the second table is not displaying records accordingly. </p>
  
  <p>This is my stored procedure. Where am I going wrong?</p>
  
  <pre><code>CREATE PROCEDURE sp_PagedItems
  (@Page int,
   @RecsPerPage int)
  AS
     SET NOCOUNT ON
  
     CREATE TABLE #TempItems
     (
          ID int IDENTITY,
          Name varchar(50),
          Price int
     )  
  
     INSERT INTO #TempItems (Name, Price)
        SELECT 
           Name, Price 
        FROM tblItems 
        ORDER BY Price  
  
     CREATE TABLE #TempItems1
     (
          ID int IDENTITY,
          Name varchar(50),
          Price int
     ) 
  
     INSERT INTO #TempItems1 (Name, Price)
        SELECT Name, Price  
        FROM tblItems1 
        ORDER BY Price 
  
     DECLARE @FirstRec int, @LastRec int
  
     SELECT @FirstRec = (@Page - 1) * @RecsPerPage
     SELECT @LastRec = (@Page * @RecsPerPage + 1)
  
     SELECT *
     FROM #TempItems 
     WHERE ID &gt; @FirstRec AND ID &lt; @LastRec 
  
     SELECT *
     FROM #TempItems1 
     WHERE ID &gt; @FirstRec AND ID &lt; @LastRec 
  
     -- Turn NOCOUNT back OFF
     SET NOCOUNT OFF
  </code></pre>
  
  <p>Executing it:</p>
  
  <pre><code>Exec sp_PagedItems 1, 10
  </code></pre>
  
  <p>The first table displays 10 records in page 1 where as the second table displays only 7 records. </p>