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

--根据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

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

http://m.bbyears.com/shujuku/33114.html

推荐访问:mssqlserver服务无法启动