------------------------------------
--用途:分页存储过程(对有主键的表效率极高)
--说明:
------------------------------------
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(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量(查询条件过长时可能会出错,可修改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)
--用途:分页存储过程(对有主键的表效率极高)
--说明:
------------------------------------
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(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量(查询条件过长时可能会出错,可修改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)
declare @tblName varchar(255) -- 表名
declare @fldName varchar(255) -- 主键字段名
declare @PageSize int = 10
declare @PageIndex int = 1
declare @IsReCount bit = 0
declare @OrderType bit = 0
declare @strWhere varchar(1000)
declare @strSQL varchar(6000)
declare @strTmp varchar(100)
declare @strOrder varchar(400)
if @OrderType != 0
begin
set @PageSize=10;
set @PageIndex int = 1 ;
set @IsReCount bit = 0;
set @OrderType bit = 0;
set @strWhere varchar(1000) ;
set @strTmp = '<(select min';
set @strOrder = concat(' order by ', @fldName ,' desc');
end
else
begin
set @strTmp = '>(select max';
set @strOrder =concat(' order by ', @fldName ,' asc');
end
set @strSQL = concat('select top ' , @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)
太繁琐了...
mysql链接字符串用concat(arg,art);
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
"
prepare str from @sql;
execute str;
deallocate prepare str;
"
即可。
--用途:分页存储过程(对有主键的表效率极高)
--说明:
------------------------------------
CREATE PROCEDURE UP_GetRecordByPage
(
v_tblName varchar(255), -- 表名
v_fldName varchar(255), -- 主键字段名
v_PageSize int , -- 页尺寸
v_PageIndex int , -- 页码
v_IsReCount int , -- 返回记录总数, 非 0 值则返回
v_OrderType int , -- 设置排序类型, 非 0 值则降序
v_strWhere varchar(1000) -- 查询条件 (注意: 不要加 where)
)
begin
declare v_strSQL varchar(6000); -- 主语句
declare v_strTmp varchar(100); -- 临时变量(查询条件过长时可能会出错,可修改100为1000)
declare v_strOrder varchar(400); -- 排序类型
if v_OrderType != 0 then
set v_strTmp = '<(select min';
set v_strOrder = concat(' order by `',v_fldName,'` desc');
else
set v_strTmp = '>(select max';
set v_strOrder = concat(' order by `',v_fldName,'` asc');
end if;
set v_strSQL = concat('select ',' * from `',v_tblName,'` where `',v_fldName,'`',v_strTmp,'(`',v_fldName,'`) from (select ',' `',v_fldName,'` from `', v_tblName,'`',v_strOrder,' limit ',(v_PageIndex-1)*v_PageSize,') as tblTmp)', v_strOrder,' limit ',v_PageSize);
if v_strWhere != '' then
set v_strSQL = concat('select ',' * from `',v_tblName,'` where `',v_fldName,'`',v_strTmp,'(`',v_fldName,'`) from (select ',' `',v_fldName,'` from `', v_tblName,'` where ',v_strWhere,v_strOrder,' limit ',(v_PageIndex-1)*v_PageSize,') as tblTmp)', v_strOrder,' limit ',v_PageSize);
end if;
if v_PageIndex = 1 then
set v_strTmp ='';
if v_strWhere != '' then
set v_strTmp = concat(' where ',v_strWhere);
end if;
set v_strSQL = concat('select ', ' * from `',v_tblName,'`', v_strTmp,' ',v_strOrder,' limit ',v_PageSize);
end if;
if v_IsReCount != 0 then
set v_strSQL = concat('select count(*) as Total from `',v_tblName,'`',' where ',v_strWhere);
end if;
set @sql = v_strSQL;
prepare str from @sql;
execute str;
deallocate prepare str;
end;
(
v_tblName varchar(255), -- 表名
v_fldName varchar(255), -- 主键字段名
v_PageSize int , -- 页尺寸
v_PageIndex int , -- 页码
v_IsReCount int , -- 返回记录总数, 非 0 值则返回
v_OrderType int , -- 设置排序类型, 非 0 值则降序
v_strWhere varchar(1000) -- 查询条件 (注意: 不要加 where)
)
begin
declare v_strSQL varchar(6000); -- 主语句
declare v_strTmp varchar(100); -- 临时变量(查询条件过长时可能会出错,可修改100为1000)
declare v_strOrder varchar(400); -- 排序类型
if v_OrderType != 0 then
set v_strTmp = '<(select min';
set v_strOrder = concat(' order by `',v_fldName,'` desc');
else
set v_strTmp = '>(select max';
set v_strOrder = concat(' order by `',v_fldName,'` asc');
end if;
set v_strSQL = concat('select ',' * from `',v_tblName,'` where `',v_fldName,'`',v_strTmp,'(`',v_fldName,'`) from (select ',' `',v_fldName,'` from `', v_tblName,'`',v_strOrder,' limit ',(v_PageIndex-1)*v_PageSize,') as tblTmp)', v_strOrder,' limit ',v_PageSize);
if v_strWhere != '' then
set v_strSQL = concat('select ',' * from `',v_tblName,'` where `',v_fldName,'`',v_strTmp,'(`',v_fldName,'`) from (select ',' `',v_fldName,'` from `', v_tblName,'` where ',v_strWhere,v_strOrder,' limit ',(v_PageIndex-1)*v_PageSize,') as tblTmp)', v_strOrder,' limit ',v_PageSize);
end if;
if v_PageIndex = 1 then
set v_strTmp ='';
if v_strWhere != '' then
set v_strTmp = concat(' where ',v_strWhere);
end if;
set v_strSQL = concat('select ', ' * from `',v_tblName,'`', v_strTmp,' ',v_strOrder,' limit ',v_PageSize);
end if;
if v_IsReCount != 0 then
set v_strSQL = concat('select count(*) as Total from `',v_tblName,'`',' where ',v_strWhere);
end if;
set @sql = v_strSQL;
prepare str from @sql;
execute str;
deallocate prepare str;
end;