sql2005带参数的分页存储过程

USE [CustomerManagementCenter]
GO
/****** Object:  StoredProcedure [dbo].[sp_SearchCustomers]    Script Date: 11/01/2010 13:51:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Radeon Ling>
-- Create date: <2010-10-27>
-- Description: <Search the customers for customer management page>
-- =============================================
ALTER PROCEDURE [dbo].[sp_SearchCustomers]
 -- Add the parameters for the stored procedure here
(
 @SortColumns VARCHAR(100)
 , @GroupName VARCHAR(50)
 , @StartRow INT
 , @PageSize INT
 , @CustomerLevel VARCHAR(50)
 , @DataSource VARCHAR(50)
 , @CustomerName VARCHAR(50)
 , @City VARCHAR(50)
 , @MobileNumber VARCHAR(50)
)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 DECLARE @CountRows INT;

 WITH PagedResults AS
 (
  SELECT CB.CustomerLevel
    ,CB.Customerid
    ,CB.DataSource
    ,CB.CustomerName
    ,CB.City
    ,CB.MobileNumber
    ,ROW_NUMBER() OVER (ORDER BY @SortColumns) AS ResultSetRowNumber 
  FROM tb_Customers_Basic AS CB LEFT JOIN tb_Message_CustomerInGroup AS MCIG ON MCIG.Customerid = CB.Customerid
   LEFT JOIN dbo.tb_Message_CustomerGroup_Basic AS MCGB ON MCIG.GroupID = MCGB.GroupID
  WHERE CB.ActiveStatus=1
   AND
   (
    (@GroupName<>'' and MCGB.GroupName = @GroupName)
    OR (@GroupName='' and MCGB.GroupName  is null )
    OR (@GroupName='' and MCGB.GroupName  is not null )
    OR (@GroupName IS NULL AND MCGB.GroupName  is not null)
   )
   AND
   (
    (@CustomerLevel<>'' and CB.CustomerLevel = @CustomerLevel)
    OR (@CustomerLevel='' and CB.CustomerLevel  is null )
    OR (@CustomerLevel='' and CB.CustomerLevel  is not null )
    OR (@CustomerLevel IS NULL AND CB.CustomerLevel is not null)
   )
   AND
   (
    (@DataSource<>'' and CB.DataSource = @DataSource)
    OR (@DataSource='' and CB.DataSource  is null )
    OR (@DataSource='' and CB.DataSource  is not null )
    OR (@DataSource IS NULL AND CB.DataSource  is not null)
   )
   AND
   (
    (@CustomerName<>'' and CB.CustomerName = @CustomerName)
    OR (@CustomerName='' and CB.CustomerName  is null )
    OR (@CustomerName='' and CB.CustomerName  is not null )
    OR (@CustomerName IS NULL AND CB.CustomerName  is not null)
   )
   AND
   (
    (@City<>'' and CB.City = @City)
    OR (@City='' and CB.City is null )
    OR (@City='' and CB.City is not null )
    OR (@City IS NULL AND CB.City  is not null)
   )
   AND
   (
    (@MobileNumber<>'' and CB.MobileNumber = @MobileNumber)
    OR (@MobileNumber='' and CB.MobileNumber is null )
    OR (@MobileNumber='' and CB.MobileNumber is not null )
    OR (@MobileNumber IS NULL AND CB.MobileNumber  is not null)
   )
 )
 
 SELECT *
 FROM PagedResults
 WHERE PagedResults.ResultSetRowNumber > @StartRow
  AND PagedResults.ResultSetRowNumber <= (@StartRow + @PageSize);
  
 WITH PagedResults AS
 (
  SELECT CB.CustomerLevel
    ,CB.DataSource
    ,CB.CustomerName
    ,CB.City
    ,CB.MobileNumber
    ,ROW_NUMBER() OVER (ORDER BY @SortColumns) AS ResultSetRowNumber 
  FROM tb_Customers_Basic AS CB LEFT JOIN tb_Message_CustomerInGroup AS MCIG ON MCIG.Customerid = CB.Customerid
   LEFT JOIN dbo.tb_Message_CustomerGroup_Basic AS MCGB ON MCIG.GroupID = MCGB.GroupID
  WHERE CB.ActiveStatus=1
   AND
   (
    (@GroupName<>'' and MCGB.GroupName = @GroupName)
    OR (@GroupName='' and MCGB.GroupName  is null )
    OR (@GroupName='' and MCGB.GroupName  is not null )
    OR (@GroupName IS NULL AND MCGB.GroupName  is not null)
   )
   AND
   (
    (@CustomerLevel<>'' and CB.CustomerLevel = @CustomerLevel)
    OR (@CustomerLevel='' and CB.CustomerLevel  is null )
    OR (@CustomerLevel='' and CB.CustomerLevel  is not null )
    OR (@CustomerLevel IS NULL AND CB.CustomerLevel is not null)
   )
   AND
   (
    (@DataSource<>'' and CB.DataSource = @DataSource)
    OR (@DataSource='' and CB.DataSource  is null )
    OR (@DataSource='' and CB.DataSource  is not null )
    OR (@DataSource IS NULL AND CB.DataSource  is not null)
   )
   AND
   (
    (@CustomerName<>'' and CB.CustomerName = @CustomerName)
    OR (@CustomerName='' and CB.CustomerName  is null )
    OR (@CustomerName='' and CB.CustomerName  is not null )
    OR (@CustomerName IS NULL AND CB.CustomerName  is not null)
   )
   AND
   (
    (@City<>'' and CB.City = @City)
    OR (@City='' and CB.City is null )
    OR (@City='' and CB.City is not null )
    OR (@City IS NULL AND CB.City  is not null)
   )
   AND
   (
    (@MobileNumber<>'' and CB.MobileNumber = @MobileNumber)
    OR (@MobileNumber='' and CB.MobileNumber is null )
    OR (@MobileNumber='' and CB.MobileNumber is not null )
    OR (@MobileNumber IS NULL AND CB.MobileNumber  is not null)
   )
 )
 SELECT count(CustomerName) FROM PagedResults;
 
END