AspNetPager控件分页存储过程

USE [CustomerManagementCenter]
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectGroupNameByPage]    Script Date: 12/30/2010 14:25:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 

ALTER  PROCEDURE [dbo].[sp_SelectGroupNameByPage] 
-- For paging
  @Start INT
 ,@PageSize INT
 ,@ActiveStatus int 
-- For query conditions 
 ,@GroupName VARCHAR(50)
 ,@GroupIdRange varchar(8000)
AS
declare @sqlpage varchar(max),
  @sqlcount varchar(1000)
BEGIN
 SET @sqlpage='SELECT PagedTable.*
 FROM
  (
  SELECT  ROW_NUMBER()OVER (ORDER BY ModifyTime DESC)AS RowNumber
  ,Groupid
  ,GroupName
  ,ModifyTime
  ,IsShow
  ,ActiveStatus
    FROM  dbo.tb_Customer_Groups_Basic where IsShow=1 '
    if @ActiveStatus=0
  set @sqlpage=@sqlpage+' and ActiveStatus=0'
 if @ActiveStatus=1
  set @sqlpage=@sqlpage+' and ActiveStatus=1'
 if @GroupIdRange<>'' and len(@GroupIdRange)>0
  set @sqlpage=@sqlpage+' and GroupId in('+@GroupIdRange+')'
 else
  set @sqlpage=@sqlpage+' and GroupId in(''00000000-0000-0000-0000-000000000000'')'
    if @GroupName <>'' and len(@GroupName)>0
  set @sqlpage=@sqlpage+' and GroupName like ''%'+@GroupName+ '%'' '
   
    set @sqlpage=@sqlpage+')as PagedTable where PagedTable.RowNumber BETWEEN '+CONVERT(VARCHAR(18),@Start)+' AND '+CONVERT(VARCHAR(18),@Start+@PageSize-1)
   
 set @sqlpage=@sqlpage+' order by PagedTable.ModifyTime '
 
    SET @sqlcount='
    SELECT COUNT(1) AS RecordCount
    FROM  dbo.tb_Customer_Groups_Basic where IsShow=1 '
   
 if @GroupIdRange<>'' and len(@GroupIdRange)>0
  set @sqlcount=@sqlcount+' and GroupId in('+@GroupIdRange+')'
 else
  set @sqlcount=@sqlcount+' and GroupId in(''00000000-0000-0000-0000-000000000000'')'
     if @ActiveStatus=0
  set @sqlcount=@sqlcount+' and ActiveStatus=0'
 if @ActiveStatus=1
  set @sqlcount=@sqlcount+' and ActiveStatus=1'
    if @GroupName <>'' and len(@GroupName)>0
  set @sqlcount=@sqlcount+' and GroupName like ''%'+@GroupName+ '%'' '

 --PRINT (@sqlpage+'  '+@sqlcount)
  
EXEC(@sqlpage+'  '+@sqlcount)
END