黑马程序员–学习日志之存储过程实现分页效果

———————- Windows Phone 7手机开发、.Net培训、期待与您交流！ ———————-

老大让我写个分页的效果，哎，折腾了好多 终于搞好了。给大家晒晒

USE [Ros]
GO
/****** Object: StoredProcedure [dbo].[sp_Paging] Script Date: 12/31/2011 17:03:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Paging]
@strSQL varchar(max) = ”, – 如：’a.UserName,a.Password,b.id from Users a left join Roles b on a.id=b.id where a.id>0′
@strOrder varchar(max) = ”, – 排序的字段名 如：’a.id desc,a.AddTime desc’
@PageSize int = 10, – 页尺寸
@PageIndex int = 1 – 页码
AS

declare @ExecSQL varchar(max)

set @ExecSQL=’SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY ‘ + @strOrder + ‘) AS pos,’ + @strSQL + ‘) AS sp WHERE pos BETWEEN ‘+str((@PageIndex-1)*@PageSize+1)+’ AND ‘+str(@PageIndex*@PageSize)
–print @ExecSQL
exec (@ExecSQL)
页面的效果。嘿嘿。 分页还不错吧
———————- Windows Phone 7手机开发、.Net培训、期待与您交流！ ———————-

详细请查看：http://net.itheima.com/