SQLServer 分页存储过程

--DROP TABLE tangfuqiang
create table tangfuqiang(
?tfqId_int int identity(1,1) primary key,
?tfqName_nvarchar nvarchar(30),
?tfqPwd_nvarchar nvarchar(30),
?tfqRemark_nvarchar nvarchar(300)
)
go

select * from tangfuqiang
go

/*采用一般的写法插入100条记录*/
DECLARE @index int
SET @index=1
WHILE @index < 100
BEGIN
?INSERT INTO tangfuqiang VALUES('tfq'+CONVERT(nvarchar,@index),'pwd'+CONVERT(nvarchar,''+CONVERT(nvarchar,@index));
?SET @index +=1;
END

/*采用创建储存过程插入100条记录*/
--DROP PROCEDURE proc_Insert
CREATE PROCEDURE proc_Insert
AS
DECLARE @index int
SET @index=1
WHILE @index < 100
BEGIN
?INSERT INTO tangfuqiang VALUES('tfq'+CONVERT(nvarchar,@index));
?SET @index +=1;
END

/*调用储存过程*/
execute proc_Insert

?

/**
声明，这个高性能分页存储过程只适合表主键为自增类型，不适合用主键采用uniqueidentifier类型的表源代码如下
**/
create PROCEDURE pageShow
??? @tb???????? varchar(512),? --表名
??? @collist??? varchar(2048),--要查询出的字段列表,*表示全部字段
??? @top? int,???? --最多读取记录数??
??? @pagesize?? int,?????????? --每页记录数?
??? @page?????? int,?????????? --指定页
??? @condition? varchar(2048),--查询条件
??? @sql_key??? varchar(20),?? --用于排序的主键
??? @col??????? varchar(50),?? --用于排序，如：id desc (多个id desc,dt asc)
??? @orderby??? bit,?????????? --排序,0-顺序,1-倒序
??? @pages????? int OUTPUT???? --总页数
AS
??? SET NOCOUNT ON
??? Declare @sqlcount Int
??? Begin Tran
??? DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
??? IF @condition is null or rtrim(@condition)=''
???? BEGIN--没有查询条件?
???????? SET @where1=' WHERE '
???????? SET @where2='? '
???? END
??? ELSE
???? BEGIN--有查询条件?
???????? SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件?
???????? SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
???? END
??? --SET @sql='SELECT @intResult=COUNT(*) FROM '+@tb+@where2
??? SET @sql='SELECT @sqlcount=COUNT(*) FROM (select top '+ cast(@top as varchar(8)) +' '+@sql_key+' from '+ @tb + @where2 +') As tmptab'
?--print @sql
??? EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT--计算总记录数
??? SELECT @pages=CEILING((@sqlcount+0.0)/@pagesize)???????????????? --计算总页数
??? IF @page=1--第一页
? BEGIN
?? SET @sql='SELECT TOP '+CAST(@pagesize AS varchar(10))+' '+@collist+' FROM '+@tb+???
??? @where2+'ORDER BY '+ @col
? END
?Else
? BEGIN
?? IF @orderby=0?
??? SET @sql='SELECT TOP '+CAST(@pagesize AS varchar(10))+' '+@collist+ ' FROM '+@tb+@where1+@sql_key+'>(SELECT MAX('+@sql_key+') '+? ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar(10))+' '+?????????
???? @sql_key+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
?? ELSE?
??? SET @sql='SELECT TOP '+CAST(@pagesize AS varchar(10))+' '+@collist+' FROM '+@tb+@where1+@sql_key+'<(SELECT MIN('+@sql_key+') '+ ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar(10))+' '+
??? @sql_key+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col+''
? END
??? EXEC(@sql)
??? --print @sql
??? If @@Error <> 0
???? Begin
???????? RollBack Tran
???????? Return -1
???? End
??? Else
???? Begin
???????? Commit Tran
???????? Return @sqlcount
???? End

/*调用分页储存过程*/ EXECUTE pageShow 'tangfuqiang','*',10,5,1,'','tfqId_int',80

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!