sqlServer存储过程通用分页

发布时间：2020-12-12 14:51:09 所属栏目：MsSql教程来源：网络整理

导读：关键字：sqlServer存储过程通用分页说明：java调用程序，请参照上一篇oracle存储过程通用分中的程序 sql： CREATE PROCEDURE GetRecordFromPage?? ??? @tblName????? varchar(255),?????? -- 表名?? ??? @fldName????? varchar(255),?????? -- 字段名?? ??

关键字：sqlServer存储过程通用分页说明：java调用程序，请参照上一篇oracle存储过程通用分中的程序 sql： CREATE PROCEDURE GetRecordFromPage?? ??? @tblName????? varchar(255),?????? -- 表名?? ??? @fldName????? varchar(255),?????? -- 字段名?? ??? @PageSize???? int = 10,?????????? -- 页尺寸?? ??? @PageIndex??? int = 1,??????????? -- 页码?? ??? @IsCount????? bit = 0,??????????? -- 返回记录总数,非 0 值则返回?? ??? @OrderType??? bit = 0,??????????? -- 设置排序类型,非 0 值则降序?? ??? @strWhere???? varchar(1000) = ''? -- 查询条件 (注意: 不要加 where)?? AS? ? declare @strSQL?? varchar(6000)?????? -- 主语句?? declare @strTmp?? varchar(100)??????? -- 临时变量?? declare @strOrder varchar(400)??????? -- 排序类型?? ? if @OrderType != 0?? begin? ??? set @strTmp = "<(select min"? ??? set @strOrder = " order by [" + @fldName +"] desc"? end? else? begin? ??? set @strTmp = ">(select max"? ??? set @strOrder = " order by [" + @fldName +"] asc"? end? ? set @strSQL = "select top " + str(@PageSize) + " * from ["? ??? + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["? ??? + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["? ??? + @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"? ??? + @strOrder?? ? if @strWhere != ''? ??? set @strSQL = "select top " + str(@PageSize) + " * from ["? ??????? + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["? ??????? + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["? ??????? + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "? ??????? + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder?? ? if @PageIndex = 1?? begin? ??? set @strTmp = ""? ??? if @strWhere != ''? ??????? set @strTmp = " where " + @strWhere?? ? ??? set @strSQL = "select top " + str(@PageSize) + " * from ["? ??????? + @tblName + "]" + @strTmp + " " + @strOrder?? end? ? if @IsCount != 0?? ??? set @strSQL = "select count(*) as Total from [" + @tblName + "]"? ? exec (@strSQL)?? ? GO?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!