ALTER proc [dbo].[Z_proc_page]
(
@table varchar(8000), --表名
@index int=1, --页码
@pageSize int=10, --显示条数
@orderBy varchar(100), --排序字段
@ascOrDesc int=1, --倒序还是正序(0正序,1倒序)
@strWhere varchar(1500)='', -- 查询条件
@showColumn nvarchar(4000)=' * ', --显示在table上的列,如果为空那么以传入的查询语句,作为输出的列
@ReturnCount int output -- 返回记录总数
)
as
begin
declare @sqlStr Nvarchar(4000) -- 主语句
declare @strOrder varchar(400) -- 排序类型
if (@showColumn is null or @showColumn='')
set @showColumn=' * '
--排序
if @ascOrDesc != 0
begin
set @strOrder = ' order by ' + @orderBy +' desc'
end
else
begin
set @strOrder = ' order by ' + @orderBy +' asc'
end
--得到数据
if len(@table)>30 --直接传输语句
begin
--记录总数
set @sqlStr = 'select @count=count(*) from (' + @table + ') temp where 1=1 '+@strWhere
--得到数据
set @sqlStr=@sqlStr+' SELECT '+@showColumn+' FROM
(select *,ROW_NUMBER() OVER('+ @strOrder+') as row from ('+@table+') temp where 1=1 '+ @strWhere+') query
where query.row > '+str((@index-1)*@pageSize)+' and query.row<='+str(@index*@pageSize)
end
ELSE
begin
print @sqlStr
--记录总数
set @sqlStr = 'select @count= count(*) from ' + @table + ' where 1=1 '+@strWhere
--得到数据
set @sqlStr=@sqlStr+' SELECT '+@showColumn+' FROM
(select *,ROW_NUMBER() OVER('+ @strOrder+') as row from '+@table+' where 1=1 '+ @strWhere+' ) query
where query.row > '+str((@index-1)*@pageSize)+' and query.row<='+str(@index*@pageSize)
end
-- begin
/*set @sqlStr='SELECT A.* FROM
(
select ROW_NUMBER() OVER('+ @strOrder+') as row,'+@unique+'
from '+@table+' where '+@strWhere+
' ) query,
(select * from '+@table+' where '+@strWhere+
') A
where A.'+@unique+'=query.'+@unique+' and query.row > '+str((@index-1)*@pageSize)+' and query.row<='+str(@index*@pageSize)+@strOrder*/
-- end
print @sqlStr
-- exec (@sqlStr)
EXEC
sp_executesql @sqlStr,
N'@count INT OUTPUT',
@ReturnCount OUTPUT
--select @ReturnCount ReturnCount
end
(
@table varchar(8000), --表名
@index int=1, --页码
@pageSize int=10, --显示条数
@orderBy varchar(100), --排序字段
@ascOrDesc int=1, --倒序还是正序(0正序,1倒序)
@strWhere varchar(1500)='', -- 查询条件
@showColumn nvarchar(4000)=' * ', --显示在table上的列,如果为空那么以传入的查询语句,作为输出的列
@ReturnCount int output -- 返回记录总数
)
as
begin
declare @sqlStr Nvarchar(4000) -- 主语句
declare @strOrder varchar(400) -- 排序类型
if (@showColumn is null or @showColumn='')
set @showColumn=' * '
--排序
if @ascOrDesc != 0
begin
set @strOrder = ' order by ' + @orderBy +' desc'
end
else
begin
set @strOrder = ' order by ' + @orderBy +' asc'
end
--得到数据
if len(@table)>30 --直接传输语句
begin
--记录总数
set @sqlStr = 'select @count=count(*) from (' + @table + ') temp where 1=1 '+@strWhere
--得到数据
set @sqlStr=@sqlStr+' SELECT '+@showColumn+' FROM
(select *,ROW_NUMBER() OVER('+ @strOrder+') as row from ('+@table+') temp where 1=1 '+ @strWhere+') query
where query.row > '+str((@index-1)*@pageSize)+' and query.row<='+str(@index*@pageSize)
end
ELSE
begin
print @sqlStr
--记录总数
set @sqlStr = 'select @count= count(*) from ' + @table + ' where 1=1 '+@strWhere
--得到数据
set @sqlStr=@sqlStr+' SELECT '+@showColumn+' FROM
(select *,ROW_NUMBER() OVER('+ @strOrder+') as row from '+@table+' where 1=1 '+ @strWhere+' ) query
where query.row > '+str((@index-1)*@pageSize)+' and query.row<='+str(@index*@pageSize)
end
-- begin
/*set @sqlStr='SELECT A.* FROM
(
select ROW_NUMBER() OVER('+ @strOrder+') as row,'+@unique+'
from '+@table+' where '+@strWhere+
' ) query,
(select * from '+@table+' where '+@strWhere+
') A
where A.'+@unique+'=query.'+@unique+' and query.row > '+str((@index-1)*@pageSize)+' and query.row<='+str(@index*@pageSize)+@strOrder*/
-- end
print @sqlStr
-- exec (@sqlStr)
EXEC
sp_executesql @sqlStr,
N'@count INT OUTPUT',
@ReturnCount OUTPUT
--select @ReturnCount ReturnCount
end