UP_GetRecordByPage
2024-10-18 05:09:49
CREATE PROCEDURE [dbo].[UP_GetRecordByPage]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(max) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(max) -- 主语句
declare @strTmp varchar(max) -- 临时变量(查询条件过长时可能会出错,可修改100为1000)
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 @IsReCount != 0
--set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
--exec (@strSQL) if @IsReCount != 0
begin
if @strWhere != ''
set @strSQL = @strSQL + ';' + 'select count(*) as Total from [' + @tblName + ']' + ' where ' + @strWhere
else
set @strSQL = @strSQL + ';' + 'select count(*) as Total from [' + @tblName + ']'
end exec (@strSQL)
select @strSQL
最新文章
- Linux学习内容
- 剑指Offer面试题:16.合并两个排序的链表
- APIPA(Automatic Private IP Addressing,自动专用IP寻址)
- C语言学习010:fopen读写文件
- 关于只针对ie7浏览器的css问题
- SQL:with ties
- Linear Regression
- UMA - Unity Multipurpose Avatar
- keyboard splitting bug on ipad with ios 5 and 6 (Cocos2d-x)
- Storm官方文档翻译之创建Storm项目
- 纯css美化单选、复选框
- Weblogic Exception in AppMerge flows&#39; progression
- Asp.Net Core 轻松学-基于微服务的后台任务调度管理器
- Ubuntu下的 PPPoE 拨号上网方法
- Coursera, Deep Learning 5, Sequence Models, week3, Sequence models &; Attention mechanism
- Fiddler的安装与使用
- 具有相同名称 的类/接口已在使用。请使用类定制设置来解决此冲突。java调用第三方的webservice应用实例
- python序列元素引用容易出错的地方
- ES6基础三(对象)
- Linux 编译时内存不足