存储过程分页

USE [CustomerManagementCenterTest]
GO
/****** Object:  StoredProcedure [dbo].[sp_QueryCityInformationByPage]    Script Date: 03/16/2011 12:04:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  <Andy Shen>
-- Create date: <2010-11-2>
-- Description: <查询城市基本信息>
-- =============================================
ALTER  PROCEDURE [dbo].[sp_QueryCityInformationByPage]
 @Start INT,
 @PageSize INT,
 @CityName varchar(50)
AS
declare @sql varchar(4000)
BEGIN
 SET @sql=' SELECT PagedTable.*
 FROM
  (
  SELECT ROW_NUMBER()OVER (ORDER BY CityName asc)AS RowNumber
   ,Cityid
   ,CityName
   ,CreatePerson
   ,ModifyPerson
   ,ModifyTime
    FROM tb_City_Basic
    )as PagedTable 
    where PagedTable.RowNumber BETWEEN '+CONVERT(VARCHAR(18),@Start)+' AND '+CONVERT(VARCHAR(18),@Start+@PageSize-1)
   if @CityName<>'' and len(@CityName)>0
  set @sql=@sql+' and CityName LIKE ''%'+@CityName+'%'' '
  set @sql=@sql+' order by CityName '

 
    SET @sql=@sql+' SELECT COUNT(1) AS RecordCount
 FROM
 (
  SELECT ROW_NUMBER()OVER (ORDER BY CityName asc)AS RowNumber
   ,Cityid
   ,CityName
   ,CreatePerson
   ,ModifyPerson
   ,ModifyTime
    FROM tb_City_Basic)
    as PagedTable where 1=1 '
   if @CityName<>'' and len(@CityName)>0
  set @sql=@sql+' and CityName LIKE ''%'+@CityName+'%'' '
 EXEC(@sql);
END