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