使用sqlserver2000存储过程分页小例

最新推荐文章于 2023-09-17 12:44:29 发布

cao_david

最新推荐文章于 2023-09-17 12:44:29 发布

阅读量572

点赞数

分类专栏： asp.net 文章标签： sqlserver 存储 join go

本文链接：https://blog.csdn.net/cao_david/article/details/1651587

版权

asp.net 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

CREATE PROCEDURE spu_SELECT_All_ARTICLE_BY_ARTICLECLASS_PAGE
(
@articleClass int,
@fldName varchar(15)='createTime',
@PageSize     int = 10,           -- 每页显示记录数
     @PageIndex    int = 1,            -- 页码
    @IsReCount    bit = 0           -- 返回记录总数, 非 0 值则返回
)
AS
declare @strSQL   varchar(600)       -- 主语句
declare @strTmp   varchar(30)        -- 临时变量
declare @strOrder varchar(40)        -- 排序类型
declare @OrderType    bit           -- 设置排序类型, 非 0 值则降序
declare @strWhere varchar(50)     -- 查询条件 (注意: 不要加 where)

--add by david
set @OrderType='1'
set @strWhere='a.father=0 and a.articleClass='+str(@articleClass)
--end add

if @OrderType != 0
begin
    set @strTmp = '<(select min'
    set @strOrder = ' order by a.'+@fldName+' desc'
end
else
begin
    set @strTmp = '>(select max'
    set @strOrder = ' order by a.'+@fldName+' asc'
end
if @PageIndex = 1--查询第一页数据
begin
    set @strSQL = 'select top '+str(@PageSize) +' a.articleID, a.articleTitle, a.createTime, a.definedID, articleClass.className, a.hits, a.userID, a.nickname, '
    set @strSQL = @strSQL +'(SELECT COUNT(articleID) AS total FROM article WHERE father = a.articleID) AS critiqueNum FROM article a INNER JOIN articleClass ON a.articleClass = articleClass.articleclass WHERE ' + @strWhere + ' ' + @strOrder
end
else --查询返回第@PageIndex页数据
begin
       set @strSQL = 'select top '+str(@PageSize) +' a.articleID, a.articleTitle, a.createTime, a.definedID, articleClass.className, a.hits, a.userID, a.nickname, '
    set @strSQL = @strSQL +'(SELECT COUNT(articleID) AS total FROM article WHERE father = a.articleID) AS critiqueNum FROM article a INNER JOIN articleClass ON a.articleClass = articleClass.articleclass WHERE a.'+@fldName + @strTmp + '(['
        + @fldName+']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName+'] from [article] where father=0 and articleClass='+str(@articleClass)+' order by '+@fldName+' desc'
        + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end

if @IsReCount != 0 --查询记录总条数
set @strSQL = 'SELECT COUNT(articleID) AS total FROM article where father=0 and articleClass='+str(@articleClass)
print(@strSQL)
exec (@strSQL)