sqlserver通用存储过程

最新推荐文章于 2025-09-11 20:53:34 发布

weixin_30346033

最新推荐文章于 2025-09-11 20:53:34 发布

阅读量75

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/majiabin/p/4911476.html

本文介绍了一个通用的SQL分页存储过程实现方法，通过参数化的形式灵活地支持不同表及排序方式的分页查询，适用于多种数据库应用环境。

USE [TnpeemSDB]
GO
/****** Object:  StoredProcedure [dbo].[pro_GeneralPaged]    Script Date: 10/26/2015 15:58:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Maticsoft.BLL.list.Lists pro = new Maticsoft.BLL.list.Lists();
 -- 调用方法 DataSet ds = pro.ByPro(PageSize, PageIndex, “查询条件”, ”需要分页的表名”, ”需要分页排序的列名”);
 -- 存储过程方法名固定

 
ALTER procedure [dbo].[pro_GeneralPaged]
   @PageIndex Int,--页码
   @PageSize Int,--每页显示记录
   @StrWhere nvarchar(1000),--Where条件
   @TableName nvarchar(1000), --进行分页的表
   @RowName nvarchar(1000),   --进行分页排序的字段
   @RecordCount Int out,--总记录数
   @PageCount Int out --总页数
   as
   
   declare @SQLSTR nvarchar(4000) --查询语句
   
   IF @RecordCount is null
	BEGIN
		DECLARE @sql nvarchar(4000)
		SET @sql=N'select @RecordCount=count(*)'
			+N' from '+ @TableName +' where 1=1 '
			+N' '+@StrWhere
		EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount output
	END
	set @PageCount =ceiling(@RecordCount*1.0/@PageSize)
	if @PageIndex>@PageCount
	begin
		set @PageIndex=@PageCount
	end
   if @PageIndex<1
	begin
		set @PageIndex=1
	end
	
if @PageIndex=1 or @PageCount<=1
	begin
		set @SQLSTR='select top '+str(@PageSize)+' * from '+ @TableName +' where 1=1 '+ @StrWhere +' order by '+@RowName+' desc'
	end 
	else if @PageIndex=@PageCount
	begin
		set @SQLSTR='select * from (select top '+ str(@RecordCount-@PageSize*(@PageIndex-1))+' * from '+ @TableName +' where 1=1 '+ @StrWhere + ' order by '+@RowName+' asc)  TempTable order by '+@RowName+' desc'
	end
	else 
	begin
		set @SQLSTR='select top '+str(@PageSize)+' * from (select top '+str(@RecordCount-@PageSize*(@PageIndex-1))+' * from '+ @TableName +' where 1=1 '+@StrWhere+'order by '+@RowName+' asc) TempTable order by '+@RowName+' desc'
	end
	set @SQLSTR=@SQLSTR +' select rcount = '+str (@RecordCount)+',pcount='+ str(@PageCount)
	exec (@SQLSTR)

转载于:https://www.cnblogs.com/majiabin/p/4911476.html