1,场景：根据学生编号查询，返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。 复制代码 代码如下: CREATE PROC [dbo].[Sp_testpageran
1,场景：根据学生编号查询，返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。 复制代码 代码如下: CREATE PROC [dbo].[Sp_testpagerandsorting] (@GroupID INT, @CurrentId INT, @TimeFrom DATETIME, @TimeTo DATETIME, @OrderBy CHAR(50), @PageSize INT, @CurrentPage INT) AS SET nocount ON BEGIN DECLARE @StartNumber INT, @EndNumber INT, @CurrentIdRowNumber INT, @RecordCount INT, @EndPageIndex INT DECLARE @RowNumberTable TABLE ( rownumber INT IDENTITY (1, 1), id INT ) –step 1: Build sort id list ——————————————————- INSERT INTO @RowNumberTable (id) SELECT sm.id AS id FROM dbo.test sm WITH (nolock) WHERE indate BETWEEN Coalesce(@TimeFrom, indate) AND Coalesce(@TimeTo, indate) AND sm.groupid = @GroupID ORDER BY CASE WHEN @OrderBy = ‘InDate desc’ THEN ( Row_number() OVER (ORDER BY indate DESC)) WHEN @OrderBy = ‘InDate asc’ THEN (Row_number() OVER (ORDER BY indate ASC)) WHEN @OrderBy = ‘Id asc’ THEN (Row_number() OVER (ORDER BY sm.id ASC)) WHEN @OrderBy = ‘Id desc’ THEN (Row_number() OVER (ORDER BY sm.id DESC)) WHEN @OrderBy = ‘Name asc’ THEN (Row_number() OVER (ORDER BY sm.name ASC)) WHEN @OrderBy = ‘Name desc’ THEN (Row_number() OVER (ORDER BY sm.name DESC) ) END –step 2: Reset page index with current id —————————————– IF @CurrentIdNumber > 0 BEGIN SELECT TOP 1 @CurrentIdRowNumber = rownumber FROM @RowNumberTable WHERE id = @CurrentIdNumber IF @CurrentIdRowNumber > 0 BEGIN IF @CurrentPage = 0 BEGIN SET @CurrentPage = Ceiling(CAST(@CurrentIdRowNumber AS DECIMAL) / CAST (@PageSize AS DECIMAL)) END END END ELSE BEGIN IF @CurrentPage = 0 BEGIN SET @CurrentPage = 1 END END –step 3: Set recordCount —————————————– SELECT @RecordCount = COUNT(1) FROM @RowNumberTable –step 4: Calc startNumber & endNumber —————————————– SELECT @StartNumber = @PageSize * ( @CurrentPage – 1 ), @EndNumber = @PageSize * ( @CurrentPage – 1 ) + @pageSize, @EndPageIndex = Ceiling(CAST(@RecordCount AS DECIMAL) / CAST(@PageSize AS DECIMAL)) IF @CurrentPage = @EndPageIndex BEGIN SET @EndNumber = @RecordCount END –step 5: Get sorted id of current page —————————————– ;WITH a AS (SELECT TOP (@EndNumber – @StartNumber) id, rownumber FROM (SELECT TOP (@EndNumber) id, rownumber FROM @RowNumberTable) AS b ORDER BY rownumber DESC) –step 6: Return current page idList ——————————————————- SELECT [ID], [GroupID] [Name], [Address] FROM dbo.test sm WITH(nolock) INNER JOIN a ON a.id = sm.id ORDER BY a.rownumber — step 7:return current page & record count ———————————- SELECT @CurrentPage AS currentpage, @RecordCount AS recordcount END 2,简单条件的,动态where语句(关于Like查询的动态where，建议使用笨办法做) 复制代码 代码如下: CREATE PROC [dbo].[Getstudentlistbycondition] @Name NVARCHAR(20), @Class INT AS SET nocount ON BEGIN BEGIN SELECT [Name], [class] FROM [testtable] WHERE [Class] = CASE WHEN @Class > 0 THEN @Class ELSE [Class] END AND [name] = CASE WHEN @Name <> ” THEN @Name ELSE [Name] END END END
