分页

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

 

 

ALTER PROCEDURE [dbo].[GetNewsTitleList]
-- Add the parameters for the stored procedure here
@tblName varchar(2024), -- 表名
@fldName varchar(1024), -- 字段名
@OrderfldName varchar(555), -- 排序字段名
@StatfldName varchar(255), -- 统计字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2048) = '' -- 查询条件 (注意: 不要加 where)
AS

SET NOCOUNT ON

DECLARE @OrderfldName2 varchar(555)

SET @OrderfldName2 = @OrderfldName

IF @OrderType <> 0 BEGIN
SET @OrderfldName = 'ORDER BY '+ @OrderfldName + ' DESC'
END

IF @OrderType = 0 BEGIN
SET @OrderfldName = 'ORDER BY '+ @OrderfldName + ' ASC'
END

BEGIN TRY
EXEC spGetList @tblName=@tblName,@fldName=@fldName,@PageSize=@PageSize,@PageIndex=@PageIndex,@IsCount=@IsCount,@orderStr=@OrderfldName,@strWhere = @strWhere
END TRY
BEGIN CATCH

declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
    declare @OrderfldName1 as varchar(50)

SET @OrderfldName = @OrderfldName2

if(@OrderfldName<>'' and charindex('.',@OrderfldName)>0)
Begin
   set @OrderfldName1=substring(@orderfldName,charindex('.',@orderfldName)+1,len(@Orderfldname))
End
Else
   set @OrderfldName1=@OrderfldName

if @OrderType != 0
begin
set @strTmp = '<(select min('
set @strOrder = ' order by ' + @OrderfldName +' desc'
end
else
begin
set @strTmp = '>(select max('
set @strOrder = ' order by ' + @OrderfldName +' asc'
end

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from '
+ @tblName + ' where ' + @OrderfldName + @strTmp
+ @OrderfldName1 + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @OrderfldName + ' from ' + @tblName + @strOrder + ') as tblTmp)'
+ @strOrder

if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from '
+ @tblName + ' where ' + @OrderfldName + @strTmp
+ @OrderfldName1 + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '
+ @OrderfldName + ' 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) + ' ' + @fldName + ' from '
+ @tblName + ' ' + @strTmp + ' ' + @strOrder
end

if @IsCount != 0 and @strWhere<>''
set @strSQL = 'select count(' + @StatfldName + ') as Total from ' + @tblName + ' where ' + @strWhere

if @IsCount != 0 and @strWhere=''
set @strSQL = 'select count(' + @StatfldName + ') as Total from ' + @tblName

print @strSQL
exec (@strSQL)
END CATCH

RETURN