Paging with this type of SPROC

Transact-SQL
1

Hi all,
I am taking an old SPROC and trying to make it work with a new system.
The SPROC and the old system work in not an efficient way as it grabs all the records so I want to page.

I have done paging before but not quite like this and need some help

Here is the original SPROC and I want to keep it like this:

	SELECT	(SELECT     SUM(costPerUnit * quantity)
		FROM          travelExpense
		WHERE      (fkTravelID = tr.[id])) AS total,
		(SELECT     SUM(costPerUnit * quantity)
		FROM          travelExpense
		WHERE      (fkTravelID = tr.[id]) AND (NOT LEFT(expenseType,7) = 'ON P.O.') ) AS totalExpensed,
		(SELECT     SUM(amount)
		FROM          travelBudgetCode
		WHERE      (fkRequestID = tr.[id])) AS totalBudgeted,
		tr.[id],
		tr.dateCreated,
		tr.fkEmployeeID,
		tr.fkPreparerID,
		tr.currentPosition,
		tr.currentLocation,
		tr.destination,
		tr.[description],
		tr.numStudents,
		tr.dateLeaving,
		tr.dateReturning,
		tr.numDaysAbsent,
		tr.numSubDays,
		tr.numTotal,
		tr.notes,
		tr.empApproval,	
		tr.empApprovalTimeStamp,
		tr.fkCurrentPrincipal,
		tr.pplApproval,
		tr.pplApprovalTimeStamp,
		tr.fkCurrentExecutive,
		tr.execApproval,
		tr.execApprovalTimeStamp,
		tr.proofRead,
		tr.proofReadTimeStamp,
		tr.encumbered,
		tr.encumberedTimeStamp,
		tr.empFinalApproval,
		tr.empFinalApprovalTimeStamp,
		tr.accounted,
		tr.accountedTimeStamp,
		tr.denied,
		tr.deniedTimeStamp,
		tr.cancelled,
		tr.cancelledTimeStamp,
		tr.advance,
		tr.advanceAmount,
		tr.advancePrint,
		emp.efirst,
		emp.elast,
		emp2.efirst AS principalFirst,
		emp2.elast AS principalLast,
		emp1.efirst AS executiveFirst,
		emp1.elast AS executiveLast,
		emp3.efirst AS preparerFirst,
		emp3.elast AS preparerLast
	FROM	dbo.travelRequest tr
		INNER JOIN dbo.staffEmployee emp
			ON tr.fkEmployeeID = emp.eid
		LEFT OUTER JOIN dbo.staffEmployee emp1
			ON tr.fkCurrentExecutive = emp1.eid
		LEFT OUTER JOIN dbo.staffEmployee emp2
			ON tr.fkCurrentPrincipal = emp2.eid
		LEFT OUTER JOIN dbo.staffEmployee emp3
			ON tr.fkPreparerID = emp3.eid
	ORDER BY tr.dateCreated DESC

Now I want to be able to give it the parameters:

  • PageNumber
  • ItemsPerPage
  • SortOnColumn
  • SortDirection
  • ExactMatch (bit)
  • SearchText

adding params is easy but to leverage it is difficult. I want to ideally NOT use dynamic SQL here.

I want the ability to be able to sort based on a given column name (and direction to sort in) and optionally the text they want to search on (with a LIKE or exact match based on the bit value)

And of course, paging the results based on the page number provided and records per page.

I am not quite sure the syntax and hoping someone can be as kind to help here.

using SQL Server 2012 I believe. I am just taking the existing SPROC and altering it to use in the new system as is with the changes.

Thank you!

2

It would be very helpfull if you provide us a working sample. Don't use believe in a professional environment, you know something or you don't. SQL Server 2012 Extended Support will end on July 12, 2022. Not July 2023, July 2022.

Order and Search:

You can use a case statement in the where clausule:
Using a Case Statement in a Where Clause (navicat.com)

Paging the results:
Pagination in SQL Server (sqlshack.com)

3

What do you mean by a working sample?
The sproc above is the sproc currently working but need to alter it to do paging with parameters mentioned. :blush:

4

Just a gentle bump in the hopes our forum members understand and can assist :slight_smile:

5

If you provide a working example so I can run your code on my machine I can help you.

You need to provide a table structure and some sample data. The testresult you have so far and what kind of result you're expecting.

6