This post is part of a series dedicated to database access. Until now all stored procedures (SP) have been pretty easy to understand and to build. This SP is the complex one of the lot. Its tasks are to obtain a page of records (rows) from a datatable ordered by a column (ascending or descending), and filter them by some criteria. All of these tasks have to be done maintaining good or decent performance.

As you can see in the SP QUERY there are different operations hence these operations can be done in different ways. Choosing one way or another one depends on the query type (with or without JOINS), the number of rows in the datatable (hundreds, thousands, millions...) and the number of parameters that can filter the datable (you can filter by one, two (not many) or you can filter a lot (by nineteen columns for example). Depending on these criteria we have to use one kind of SP QUERY or another.

Maybe this is a little confusing, but in future posts I will deal with these different QUERY methods, their meanings and their motivations: CTE RowNumber, CTE DenseRank, Temporal Table with dynamic WHERE.