存储过程分页

来源：互联网发布：网络老虎机辅助软件编辑：程序博客网时间：2024/06/11 14:08

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