Grid Paging and dynamic ordering with ORDER by CASE statement

If you are writing dynamic query and using "EXEC" statement to get paging and dynamic ordering support, you will have performance and security problems.

SQL Server 2005 and above supports Case statement in ordering. It also has partial table expression statements. You can use both of those to build powerful queries for your grid data.

First example: dynamic ordering and row numbering

   1:  DECLARE @temp table

   2:  (ShipNo int,

   3:   Supplier varchar(10),

   4:   Shiptime datetime

   5:  )

   6:   

   7:  select dateadd(day,5,getdate())

   8:   

   9:  INSERT INTO @temp VALUES (1,'WRWE' ,dateadd(day,1,getdate()))

  10:  INSERT INTO @temp VALUES (1, 'AXTFA', dateadd(day,3,getdate()))

  11:  INSERT INTO @temp VALUES (2, 'ABLRA',dateadd(day,5,getdate()))

  12:  INSERT INTO @temp VALUES (2, 'CADRB',dateadd(day,8,getdate()))

  13:  INSERT INTO @temp VALUES (3, 'BGRTEE',dateadd(day,15,getdate()))

  14:  INSERT INTO @temp VALUES (4, 'AAAAAA',dateadd(day,25,getdate()))

  15:   

  16:  Declare @OrderBy nvarchar(255)

  17:  DECLARE @SortDir nvarchar(5)

  18:   

  19:  SET @OrderBy = 'Supplier';

  20:  SET @SortDir = 'asccc';

  21:   

  22:  --SQL 2005 and above 

  23:   

  24:  --separate data columns for different data types in cases

  25:  SELECT ROW_NUMBER() OVER (ORDER BY 

  26:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'Supplier' THEN Supplier END ASC,

  27:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'ShipNo' THEN ShipNo END ASC,

  28:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'Shiptime' THEN Shiptime END ASC,

  29:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'Supplier' THEN Supplier END desc,

  30:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'ShipNo' THEN ShipNo END desc,

  31:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'Shiptime' THEN Shiptime END desc,

  32:   

  33:   CASE WHEN 1=1 then Shipno end --default

  34:   )

  35:  AS RowNo,

  36:  * --get all

  37:  FROM @temp

  38:  ;

Next, we will add paging elements into our query. We need to use rownumber in where clause to make paging work.

   1:   

   2:   

   3:  --for paging you can take this in subset and specify rowno

   4:  DECLARE @rowstart int

   5:  DECLARE @rowend int

   6:  set @rowstart = 1;

   7:  set @rowend = 3; --paging support

   8:   

   9:  ;

  10:  with subset as

  11:           (

  12:           SELECT ROW_NUMBER() OVER (ORDER BY 

  13:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'Supplier' THEN Supplier END ASC,

  14:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'ShipNo' THEN ShipNo END ASC,

  15:   CASE WHEN @SortDir = 'asc' and @OrderBy = 'Shiptime' THEN Shiptime END ASC,

  16:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'Supplier' THEN Supplier END desc,

  17:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'ShipNo' THEN ShipNo END desc,

  18:   CASE WHEN @SortDir = 'desc' and @OrderBy = 'Shiptime' THEN Shiptime END desc,

  19:   

  20:   CASE WHEN 1=1 then Shipno end --default

  21:   )

  22:  AS RowNo,

  23:  * --get all

  24:  FROM @temp

  25:   

  26:  )

  27:  select * from subset

  28:  where RowNo between @rowstart and @rowend

You need to separate case statement for different data type. Otherwise, you will get conversion errors.