兴發国际娱乐手机登录支持连接查询等复杂的SQL,二种MSSQL分页存储过程实例应用

if(@ItemCount 0) set @ItemCount = @ItemCount + @PageSize else set
@ItemCount = @PageSize

CREATE PROCEDURE Pagination(@SQL nvarchar(1024), –查询语句@PageSize int
= 20, –分页大小@PageIndex int = 0, –分页索引@Sort nvarchar(100) = ”,
–排序字段@TotalCount int = 0 output –总数 )AS

if(@Sort != ””)begin /*声明排序变量*/ declare @IndexSort1
nvarchar(50), @IndexSort2 nvarchar(50), @Sort1 nvarchar(50), @Sort2
nvarchar(50) SET @Sort1 = @Sort SET @Sort2 =
Replace(Replace(Replace(@Sort, ”DESC”, ”@SORT”), ”ASC”, ”DESC”),
”@SORT”, ”ASC”) print @Sort1 print @Sort2

if(@ItemCount 0) return 1

if(@ItemCount 0) return 1

exec pagination ‘news’,’*’,’n_id’,1000,2,0,0,”

set @strSQL = ” select @TotalCount=count(*) from (”+@SQL+”) as t ”

创建存储过程 CREATE PROCEDURE pagination @tblName varchar(255), — 表名
@strGetFields varchar(1000) = ‘*’, — 需要返回的列 @fldName
varchar(255)=”, — 排序的字段名(可包含如TABLE.FLDNAME形式) @PageSize
int = 10, — 页尺寸 @PageIndex int = 1, — 页码 @doCount bit = 0, —
返回记录总数, 非 0 值则返回 @OrderType bit = 0, — 设置排序类型, 非 0
值则降序 @strWhere varchar(1500) = ” — 查询条件 (注意: 不要加 where)
AS declare @strSQL varchar(5000) — 主语句 declare @strTmp varchar(110)
— 临时变量 declare @strOrder varchar(400) — 排序类型 declare
@fldName_t varchar(255) —
在分页时用的排序字段名,不包含多表并列时的表名 set @fldName_t =
right(@fldName,len(@fldName)-CHARINDEX(‘.’,@fldName)) if @doCount != 0
begin if @strWhere !=” set @strSQL = ‘select count(*) as Total from ‘

set @strSQL = ”SELECT * FROM (SELECT TOP ” + STR(@ItemCount) + ” *
FROM (SELECT TOP ” + STR(@PageSize * @_PageIndex) + ” * FROM
(”+@SQL+”) AS t0 ORDER BY ”+@Sort1 +”) AS t1 ORDER BY ”+@Sort2 +”)
AS t2 ORDER BY ” +@Sort endelsebegin set @strSQL = ”SELECT * FROM
(SELECT TOP ” + STR(@ItemCount)

/body/html

set @_PageIndex = @PageIndex + 1;/*确定搜索边界*/set @ItemCount =
@TotalCount – @PageSize * @_PageIndex

if(@Sort != ”)begin /*声明排序变量*/ declare @IndexSort1
nvarchar(50), @IndexSort2 nvarchar(50), @Sort1 nvarchar(50), @Sort2
nvarchar(50) SET @Sort1 = @Sort SET @Sort2 =
Replace(Replace(Replace(@Sort, ‘DESC’, ‘@SORT’), ‘ASC’, ‘DESC’),
‘@SORT’, ‘ASC’)

declare @ItemCount int declare @_PageIndex int

MSSQL分页存储过程,支持连接查询等复杂的查询

set nocount on/*声明查询字符串*/declare @strSQL nvarchar(4000)

exec sp_executesql
@strSQLGO?/*存储过程好处是,函数是mssql内部函数所在运行起来是很快的。*/?

/*取得查询结果总数*/exec sp_executesql@strSQL, N”@TotalCount int=0
OUTPUT”, @TotalCount=@TotalCount OUTPUT

set @strSQL = ‘ select @TotalCount=count(*) from (‘+@SQL+’) as t ‘

CREATE PROCEDURE Pagination(@SQL nvarchar(1024), @PageSize int = 20,
–分页大小@PageIndex int = 0, –分页索引@Sort nvarchar(100) = ””,
–排序字段@TotalCount int = 0 output –总数 )AS

if(@ItemCount 0) set @ItemCount = @ItemCount + @PageSize else set
@ItemCount = @PageSize

set nocount on/*声明查询字符串*/declare @strSQL nvarchar(4000)

二种MSSQL分页存储过程实例应用html xmlns= content=text/html;
charset=gb2312 /title二种MSSQL分页存储过程实例应用/title/headbody

发表评论

电子邮件地址不会被公开。 必填项已用*标注