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
原文：http://www.cnblogs.com/TddCoding/p/3565381.html