通用存储过程

今天论坛里面找了一个通用的分页存储过程，和大家分享下.

此文转自:http://blog.csdn.net/cndotaci

 

/*通用分页存储过程*/

USE HotelManagementSystem

GO

IF EXISTS(SELECT * FROM sys.objects WHERE NAME='cndoup_GetPageOfRecords')

DROP PROCEDURE cndoup_GetPageOfRecords

GO

--创建存储过程

CREATE PROCEDURE cndoup_GetPageOfRecords

@pageSize int = 20,                        --分页大小

@currentPage int ,                         --第几页

@columns varchar(1000) = '*',              --需要得到的字段 

@tableName varchar(100),                   --需要查询的表  

@condition varchar(1000) = '',             --查询条件, 不用加where关键字

@ascColumn varchar(100) = '',              --排序的字段名 (即 order by column asc/desc)

@bitOrderType bit = 0,                     --排序的类型 (0为升序,1为降序)

@pkColumn varchar(50) = ''                 --主键名称

 

AS

BEGIN                                          --存储过程开始

DECLARE @strTemp varchar(300)

DECLARE @strSql varchar(5000)              --该存储过程最后执行的语句

DECLARE @strOrderType varchar(1000)        --排序类型语句 (order by column asc或者order by column desc)

 

BEGIN

IF @bitOrderType = 1   --降序

BEGIN

SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'

SET @strTemp = '<(SELECT min'

END

ELSE--升序

BEGIN

SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'

SET @strTemp = '>(SELECT max'

END

 

IF @currentPage = 1--第一页

BEGIN

IF @condition != ''

SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+

' WHERE '+@condition+@strOrderType

ELSE

SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType

END

 

ELSE-- 其他页

BEGIN

IF @condition !=''

SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+

' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+

' '+@pkColumn+' FROM '+@tableName+'where'+@condition+@strOrderType+') AS TabTemp)'+@strOrderType

ELSE

SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+

' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+

' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType

END

 

END

EXEC (@strSql)

END

 

--存储过程结束

 

 

--分页得到客房信息列表测试

EXEC cndoup_GetPageOfRecords 20,2,'房间号=RoomNum,

房间状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID),

房间状态=(SELECT RSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusID),

床位数=BedNum,

楼层=Floors,

描述=RoomDes,

备注=RoomRemark','Room','','RoomID',0,'RoomID'

 

 

--根据房间号得到客房信息测试

EXEC cndoup_GetPageOfRecords 1,1,'房间号=RoomNum,

房间状态=(SELECT RoomTypeDes FROM RoomType WHERE RoomTypeID=Room.RoomTypeID),

房间状态=(SELECT RSDec FROM RoomStatus WHERE RoomStatusID=Room.RoomStatusID),

BedNum,

Floors,

RoomDes,

RoomRemark','Room','RoomNum=304','RoomID',0,'RoomID'