In @SQL variable a SQL querry is passed on which I don't know. Based on this sql querry page and the size I have to return the recordset with limited records and total rows in the fetched from SQL Querry as OUT parameter. Please help me out.
-SP does have parameters - show page#, lines per page;
-create temporary table with identity;
-insert data to temporary table (include sorting);
-select and return page (not big deal if you know page# )
- select and return totals for columns (if it needs)
- output parameter- total pages.
It is very simple and very effective method (it does have potential problems but it works for many cases).