Conditional sorting and paging in T-SQL

DECLARE
@Take INT,
@Skip INT,
@SortField VARCHAR(100)

;WITH workers AS (
SELECT 
	   w.WorkerId,
	   w.FirstName,
	   w.LastName,
	   w.Specialization,    
	   ROW_NUMBER() OVER 
	   (
		  ORDER BY 			 
			 CASE WHEN @SortField = 'WorkerId#desc' THEN w.WorkerId
			 END DESC,
			 CASE WHEN @SortField = 'WorkerId#asc' THEN w.WorkerId
			 END ASC,
			 CASE WHEN @SortField = 'LastName#desc' THEN w.LastName
			 END DESC,
			 CASE WHEN @SortField = 'LastName#asc' THEN w.LastName
			 END ASC,
			 CASE WHEN @SortField = 'Specialization#desc' THEN w.Specialization
			 END DESC,
			 CASE WHEN @SortField = 'Specialization#asc' THEN w.Specialization
			 END ASC,				 
			 CASE WHEN 1 = 1 THEN w.LastName
			 END ASC
	   ) AS rowNumber
    FROM Workers w
)

    SELECT 
	   w.WorkerId,
	   w.FirstName,
	   w.LastName
	   w.Specialization,
	   (SELECT COUNT(*) FROM workers) AS TotalRows
    FROM workers w
    WHERE rowNumber BETWEEN @Skip + 1 AND @Skip + @Take
    ORDER BY rowNumber ASC

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s