Sunday, March 11, 2012

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.

No comments:

Post a Comment

Hey!
Let me know what you think?