mmssqlserver中三种分页存储过程代码

时间：2011-12-24 编辑：简简单单来源：一聚教程网

--根据MAX(MIN)ID

代码如下

复制代码

CREATE PROC [dbo].[proc_select_id]
@pageindex int=1,--当前页数
@pagesize int=10,--每页大小
@tablename VARCHAR(50)='',--表名
@fields VARCHAR(1000)='',--查询的字段集合
@keyid VARCHAR(50)='',--主键
@condition NVARCHAR(1000)='',--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
DECLARE @sql NVARCHAR(4000)
--IF(@totalRecord IS NULL)
--BEGIN
  SET @sql=N'SELECT @totalRecord=COUNT(*)'
   +N' FROM '+@tablename
   +N' WHERE '+@condition
  EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT
--END
IF(@pageindex=1)
BEGIN
  SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
  EXEC(@sql)
END
ELSE
BEGIN
  DECLARE @operatestr CHAR(3),@comparestr CHAR(1)
  SET @operatestr='MAX'
  SET @comparestr='>'
  IF(@orderstr<>'')
  BEGIN
   IF(CHARINDEX('desc',LOWER(@orderstr))<>0)
   BEGIN
    SET @operatestr='MIN'
    SET @comparestr='<'
   END
  END
  SET @sql=N'SELECT top '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@keyid+@comparestr
   +N'(SELECT '+@operatestr+N'('+@keyid+N') FROM '+@tablename+N' WHERE '+@keyid
   +N' IN (SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '+@tablename+N' WHERE '
   +@condition+N' '+@orderstr+N')) AND '+@condition+N' '+@orderstr
  EXEC(@sql)
END
GO

--根据ROW_NUMBER() OVER

代码如下

复制代码

CREATE PROC [dbo].[proc_select_page_row]
@pageindex INT=1,--当前页数
@pagesize INT=10,--每页大小
@tablename VARCHAR(50)='',--表名
@fields VARCHAR(1000)='*',--查询的字段集合
@keyid VARCHAR(50)='',--主键
@condition NVARCHAR(1000)='',--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
DECLARE @sql NVARCHAR(4000)
-- IF @totalRecord IS NULL
-- BEGIN
  SET @sql=N'SELECT @totalRecord=COUNT(*)'
   +N' FROM '+@tablename
   +N' WHERE '+@condition
  EXEC sp_executesql @sql,N'@totalRecord bigint OUTPUT',@totalRecord OUTPUT
--END
IF(@pageindex=1)
BEGIN
  SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
  EXEC(@sql)
END
ELSE
BEGIN
  DECLARE @StartRecord INT
  SET @StartRecord = (@pageindex-1)*@pagesize + 1
  SET @sql=N'SELECT * FROM (SELECT ROW_NUMBER() OVER ('+ @orderstr +N') AS rowId,'+@fields+N' FROM '+ @tablename+N') AS T WHERE rowId>='+STR(@StartRecord)+N' and rowId<='+STR(@StartRecord + @pagesize - 1)
  EXEC(@sql)
END
GO

--根据TOP ID

代码如下

复制代码

CREATE PROC [dbo].[proc_select_page_top]
@pageindex INT=1,--当前页数
@pagesize INT=10,--每页大小
@tablename VARCHAR(50)='',--表名
@fields VARCHAR(1000)='',--查询的字段集合
@keyid VARCHAR(50)='',--主键
@condition NVARCHAR(1000)='',--查询条件
@orderstr VARCHAR(500),--排序条件
@totalRecord BIGINT OUTPUT--总记录数
AS
IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '
IF ISNULL(@fields,N'')=N'' SET @fields=N'*'
IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'
DECLARE @sql NVARCHAR(4000)
--IF(@totalRecord IS NULL)
--BEGIN
  SET @sql=N'SELECT @totalRecord=COUNT(*)'
   +N' FROM '+@tablename
   +N' WHERE '+@condition
  EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT
--END
IF(@pageindex=1)
BEGIN
  SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr
  EXEC(@sql)
END
ELSE
BEGIN
  SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@keyid
  +N' NOT IN(SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '
  +@tablename+N' WHERE '+@condition+N' '+@orderstr+N') AND '+@condition+N' '+@orderstr
  EXEC(@sql)
END
GO

上一个： mysql ERROR 1045 修改密码二种方法
下一个： sqlserver2000附加数据库出错解决办法

推荐专题

最新下载

热门教程

mmssqlserver中三种分页存储过程代码

相关文章

热门栏目

php教程

asp.net教程

手机开发

css教程

网页制作

办公数码

jsp教程