Paging

USE [Demo]
GO

/****** Object: StoredProcedure [dbo].[sp_Page] Script Date: 02/19/2014 21:26:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--declare @TotalCount int 
--exec Page 8,0,'S_Name','desc','%周%','',@TotalCount output
--select @TotalCount

ALTER proc [dbo].[sp_Page]
@PageSize int,
@PageCurrent int,
@SortField varchar(20),
@SortType varchar(20),
@S_Name nvarchar(100),
@C_ID char(2),
@TotalCount int output
as
begin
declare @SelectSql nvarchar(2000)
declare @CountSql nvarchar(2000)
declare @WhereSql nvarchar(2000)
declare @StartRow int=((@PageCurrent-1)*@PageSize+1)
declare @EndRow int=@PageCurrent*@PageSize

--排序字段
if @SortField is null
begin
set @SortField=N'ID'
set @SortType=N'ASC'
end
--条件查询
set @WhereSql=N'WHERE 1=1 '

if ltrim(rtrim(@S_Name)) is not null and Ltrim(rtrim(@S_Name))<>''
set @WhereSql+=N'And S_Name like ''%'+Ltrim(rtrim(@S_Name))+'%'''
if Ltrim(rtrim(@C_ID)) is not null and Ltrim(rtrim(@C_ID))<>''
set @WhereSql +=N'AND C_ID = '+Ltrim(rtrim(@C_ID))+''

set @SelectSql=N'
select 
TT.RowId
,ID
,TT.S_Name
from (select
ROW_NUMBER() over(order by '+@SortField+' '+@SortType+' ) as RowId
,ID
,S_Name
from dbo.Student WITH(NOLOCK)
'+@WhereSql+'
)TT
where TT.RowId between '+cast(@StartRow as varchar(max))+' and '+cast(@EndRow as varchar(max))+''
set @CountSql=N'Select @TotalCount =COUNT(*) From dbo.Student A WITH(NOLOCK)'+@WhereSql



EXEC(@SelectSql)
--EXEC sp_executesql @SelectSql,
--N'@S_Name NVARCHAR(100),
-- @C_ID NVARCHAR(100),
-- @SortType NVARCHAR(100),
-- @SortField NVARCHAR(100),
-- @PageSize INT,
-- @PageCurrent INT',
-- @S_Name,
-- @C_ID,
-- @SortType,
-- @SortField,
-- @PageSize,
-- @PageCurrent
--EXEC @CountSql
EXEC sp_executesql @CountSql,
N'@S_Name NVARCHAR(100),
@C_ID char(2),
@TotalCount int output',
@S_Name,
@C_ID,
@TotalCount output 

end

GO