Pagination and Sorting

declare @SearchFilter nvarchar(200) = ?
if( len(@SearchFilter  ) =0 ) set @SearchFilter  =null

declare @OrderByColumn nvarchar(100) = ?
declare @SortOrder BIT = ? -- 0 ASC, 1 DESC

-- region Paging and ordering
-- ------------------------------------------------
declare @startIndex int = 0
declare @pageSize int = 1000

if( len(@OrderByColumn  ) =0 ) set @OrderByColumn  =null
if( @OrderByColumn is null) RAISERROR( 'OrderByColumn cannot be (null)!', 17, 0 );

-- ------------------------------------------------
-- endregion

;with baseTable as  (
SELECT DISTINCT r.Address,
                ..
                r.Forename,
                ..
                r.Postcode,
                
                r.StreetName,
                r.Surname,
								..
                iif( len(r.Address)>0, r.Address, r.StreetName+r.Town+r.Postcode)   as OrderAddress,
                r.Surname + r.Forename as OrderFullName

	FROM        ..

	WHERE       IR.IncidentId = @IncidentId
			     	  AND (
								@SearchFilter is null or
								(
									r.Address LIKE @SearchFilter
					        OR
					        r.Surname LIKE @SearchFilter
								)
							)
) -- end with baseTable

/*
	Full Name       - Sort ascending / descending order alphabetically
	Age Range       - Sort ascending / descending order according to date
	Address         - Sort ascending / descending order alphabetically
	Latest Updates  - Sort ascending / descending order according to date (assuming this is how it is stored)
	Status          - Sort ascending / descending order alphabetically
 */

select * from baseTable
	ORDER BY    CASE WHEN  @OrderByColumn = 'Full Name' and @SortOrder = 0 then OrderFullName END,
				CASE WHEN  @OrderByColumn = 'Full Name' and @SortOrder = 1 then OrderFullName END DESC,
				CASE WHEN  @OrderByColumn = 'Address' and @SortOrder = 0 then OrderAddress END,
				CASE WHEN  @OrderByColumn = 'Address' and @SortOrder = 1 then OrderAddress END DESC

	OFFSET @startIndex ROWS
	FETCH NEXT @pageSize ROWS ONLY