支持多表分页查询的存储过程

最新推荐文章于 2024-11-15 14:43:45 发布

weixin_34396902

最新推荐文章于 2024-11-15 14:43:45 发布

阅读量80

点赞数

文章标签： ui

本文转载：http://hi.baidu.com/hanrenjun/item/caa393dc811efbdd251f40ad

http://www.bhcode.net/article/20081229/3796.html

同样不错的博客：http://blog.csdn.net/lyq5655779/article/details/7328560#

usp_PagingLarge测试脚本：

USE [Northwind]
GO

--SQL语句
SELECT  Orders.OrderID ,
        Products.ProductName AS 'Name'
FROM    dbo.Orders INNER JOIN dbo.[Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN dbo.Products ON [Order Details].ProductID = Products.ProductID
ORDER BY Orders.OrderID desc,
        Products.ProductName  asc    
        
/*

  AAAA   AAAA   AAAA   AAAA  RRRRR   GGGG  HH  HH  ######
 AA  AA AA  AA AA  AA AA  AA RR  RR GG  GG HH  HH  ######
 AA  AA AA  AA AA  AA AA  AA RR  RR GG     HH  HH   ####
 AAAAAA AAAAAA AAAAAA AAAAAA RRRRR  GG GGG HHHHHH    ##
 AA  AA AA  AA AA  AA AA  AA RR  RR GG  GG HH  HH
 AA  AA AA  AA AA  AA AA  AA RR  RR  GGG G HH  HH    ##

*/


DECLARE	@return_value int

EXEC	@return_value = [dbo].[usp_PagingLarge]
		@TableNames = N'dbo.Orders INNER JOIN dbo.[Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN dbo.Products ON [Order Details].ProductID = Products.ProductID',
		@PrimaryKey = N'Orders.OrderID',
		@Fields = N'Orders.OrderID ,Products.ProductName as Name',
		@PageSize = 20,
		@CurrentPage = 2,
		@Filter = NULL,
		@Group = NULL,
		@Order = N'Orders.OrderID desc,Products.ProductName  asc'


GO

proc_ListPage测试脚本：

USE [Northwind]
GO

--SQL语句
SELECT  Orders.OrderID ,
        Products.ProductName
FROM    dbo.Orders INNER JOIN dbo.[Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN dbo.Products ON [Order Details].ProductID = Products.ProductID
ORDER BY Orders.OrderID desc,
        Products.ProductName  asc      
        

/*

  AAAA   AAAA   AAAA   AAAA  RRRRR   GGGG  HH  HH  ######
 AA  AA AA  AA AA  AA AA  AA RR  RR GG  GG HH  HH  ######
 AA  AA AA  AA AA  AA AA  AA RR  RR GG     HH  HH   ####
 AAAAAA AAAAAA AAAAAA AAAAAA RRRRR  GG GGG HHHHHH    ##
 AA  AA AA  AA AA  AA AA  AA RR  RR GG  GG HH  HH
 AA  AA AA  AA AA  AA AA  AA RR  RR  GGG G HH  HH    ##

*/

DECLARE	@return_value int,
		@pageCount int,
		@Counts int

EXEC	@return_value = [dbo].[proc_ListPage]
		@tblName = N'dbo.Orders INNER JOIN dbo.[Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN dbo.Products ON [Order Details].ProductID = Products.ProductID',
		@fldName = N'[orders].orderID,Products.ProductName',
		@pageSize = 20,
		@page = 2,
		@pageCount = @pageCount OUTPUT,
		@Counts = @Counts OUTPUT,
		@fldSort = N'[orders].orderID desc,Products.ProductName',
		@Sort = 0,--如果@fldSort字段为NULL，则@Sort也为null。
		@strCondition = NULL,
		@ID = N'Orders.OrderID',
		@Dist = 0

SELECT	@pageCount as N'@pageCount',
		@Counts as N'@Counts'


GO