One of the wonderful things introduced in SQL 2005 was the common table expression. You can read more about it here: http://msdn.microsoft.com/en-us/library/ms186243.aspx
In the following example, I’m iterating through a table that has a parent-child relationship… but how are we going to get a list of children for a given parent?
CREATE TABLE [MyTable](
[Id] [bigint] NOT NULL,
[ParentId] [bigint] NULL,
[Name] [nvarchar](32) NOT NULL,
[InsertedDate] [datetime2](7) NOT NULL
)
While I would much prefer to use EF4 to return all the children for a given node, SQL CTE makes it easier and faster in a SPROC when compared to EF4.
What is different from this implementation versus some others is the method in which paging and sorting are implemented. The typical sort ascending/descending is usually very messy, this method creates two columns using row numbers counting in opposite directions. In doing so, the final “sort direction” is handled by the paging. Take a look and let me know what you think.
Just a quick note, the pageIndex is coded such that the first page is 0.
declare @parent bigint; set @parent = '100';
declare @pageIndex int; set @pageIndex = 0;
declare @pageSize int; set @pageSize = 5;
declare @sortAscending bit; set @sortAscending = 1;
declare @sortColumn NVARCHAR(50); set @sortColumn = 'id';
WITH Children ([Id], [ParentId], [Name], [InsertedDate], Level)
AS
(
-- Anchor member definition
SELECT mt.[Id],mt.[ParentId],mt.[Name],mt.[InsertedDate], 0 as Level
FROM [MyTable] AS mt
WHERE mt.[ParentId] = @parent
UNION ALL
-- Recursive member definition
SELECT mto.[Id], mto.[ParentId], mto.[Name], mto.[InsertedDate], Level + 1
FROM [MyTable] AS mto
INNER JOIN Children AS c ON mto.[ParentId] = c.Id
)
-- Statement that executes the CTE
SELECT [Id],[ParentId],[Name],[InsertedDate],ROWNUM, ROWNUMDESC
FROM (
SELECT [Id],[ParentId],[Name],[InsertedDate], Level,
(ROW_NUMBER() OVER(order by
case @sortcolumn
when 'name' then [Name]
when 'inserteddate' then convert(VARCHAR(50),[InsertedDate],120)
when 'parentid' then convert(VARCHAR(50),[ParentId])
else convert(VARCHAR(50),[Id])
end
) - 1) AS ROWNUM,
(ROW_NUMBER() OVER(order by
case @sortcolumn
when 'name' then [Name]
when 'inserteddate' then convert(VARCHAR(50),[InsertedDate],120)
when 'parentid' then convert(VARCHAR(50),[ParentId])
else convert(VARCHAR(50),[Id])
end desc
) - 1) AS ROWNUMDESC
FROM Children) innerq
WHERE
CASE when @sortAscending = '1' then ROWNUM else ROWNUMDESC end
BETWEEN (@pageIndex * @pageSize) and (((@pageIndex + 1) * @pageSize) - 1)
ORDER BY CASE when @sortAscending = '1' then ROWNUM else ROWNUMDESC end
If you’re interested in testing this example, use the create table above and use the following test data.
insert into mytable values ('100', null, 'test01', '2011-01-01')
insert into mytable values ('101', null, 'test02', '2011-01-01')
insert into mytable values ('102', '100', 'test03', '2011-01-01')
insert into mytable values ('103', '100', 'test04', '2011-01-01')
insert into mytable values ('104', '101', 'test05', '2011-01-01')
insert into mytable values ('105', '101', 'test06', '2011-01-01')
insert into mytable values ('106', '102', 'test07', '2011-01-01')
insert into mytable values ('107', '106', 'test08', '2011-01-01')
insert into mytable values ('108', '102', 'test09', '2011-01-01')
insert into mytable values ('109', '107', 'test10', '2011-01-01')