@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@IsCount bit=0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(3000) ='', -- 查询条件 (注意: 不要加 where)
@StartIndex varchar(255),
@maxRows varchar(255)
AS
declare @strSQL varchar(3000) -- 主语句
declare @strCount varchar(3000) -- 返回数据的总条数语句
declare @strOrder varchar(3000) -- 排序类型语句
if @OrderType != 0
begin
set @strOrder = ' order by ' + @fldName +' desc'
end
else
begin
set @strOrder = ' order by ' + @fldName +' asc'
end
set @strSQL ='select * from (select *, ROW_NUMBER() OVER ('+ @strOrder +') as Pos from ' + @tblName + ') as T where T.Pos > ('+ @StartIndex +') and T.Pos <'+ @maxRows
set @strCount ='select count(*) as Total from ' + @tblName + ' as count'
if @strWhere !=''
begin
set @strSQL ='select * from (select *, ROW_NUMBER() OVER ('+ @strOrder +') as Pos from ' + @tblName + ' where ' + @strWhere + ') as T where T.Pos > ('+ @StartIndex +') and T.Pos <'+ @maxRows
set @strCount ='select count(*) as Total from ' + @tblName + ' as count where ' + @strWhere
end
if @IsCount!=0 ----按需要返回数据的总条数
begin
exec (@strCount)
end
exec (@strSQL)
