A stored procedure is perfect for this.
The technique you're looking for is called "Paging".
You will have to extend your
DataObjectForAjaxCall
object to include page number and maybe a page size (but you might want to assume 10).
function loadData(pageNum) {
if(pageNum == undefined)
pageNum = 1;
var Year = $("#year").val();
var Month = $("#month").val();
var DataObjectForAjaxCall = { strYear: Year, strMonth: Month, iPageNum: pageNum, iPageSize: 10 };
change the
loadData
action to use these new parameters to call the procedure.
In the procedure, you will need an index column:
SELECT *,
ROW_NUMBER() OVER (ORDER BY @sort DESC) AS [Index]
FROM myResults
Index
will number the rows from 1 to n. Use this in the
WHERE
clause:
WHERE [Index] BETWEEN (@pageNum-1)*@pageSize AND (@pageNum)*@pageSize
There are loads of examples of Paging online. here's a simple example:
sql - Pagination with the stored procedure - Stack Overflow[
^]