存储过程创建如下
if exists(select * from sysobjects where name='Select_Article_ByPass_ByArticleType_ByUser')
drop proc Select_Article_ByPass_ByArticleType_ByUser
go
create proc Select_Article_ByPass_ByArticleType_ByUser
@pagesize int,
@page int,
@articleTypeId int,
@isPass int,
@publisher nvarchar(50),
@totalpage int output,
@totalrow int output
as
declare @sql varchar(2000)
if(@articleTypeId=0)
begin
if(@publisher is null)
begin
select @totalrow=COUNT(*) from Article where [email protected]
end
else
begin
select @totalrow=COUNT(*) from Article where [email protected] and [email protected]
end
end
else
begin
if(@publisher is null)
begin
select @totalrow=COUNT(*) from Article where [email protected] and [email protected]
end
else
begin
select @totalrow=COUNT(*) from Article where [email protected] and [email protected] and [email protected]
end
end
set @[email protected][email protected]
if(@[email protected]>0)
begin
set @[email protected]+1
end
if(@articleTypeId=0)
begin
if(@publisher is null)
begin
set @sql='select top '+CONVERT(varchar(10),@pagesize)+' * from Article where IsPass='+CONVERT(varchar(10),@isPass)+' and
ArticleId not in (select top
('+CONVERT(varchar(10),@pagesize)+'*('+CONVERT(varchar(10),@page)+'-1)) ArticleId from Article where
IsPass='+CONVERT(varchar(10),@isPass)+' order by ArticleId)
order by ArticleId'
end
else
begin
set @sql='select top '+CONVERT(varchar(10),@pagesize)+' * from Article where IsPass='+CONVERT(varchar(10),@isPass)+' and
Publisher= ' + CONVERT(varchar(50),@publisher)+ ' and ArticleId not in (select top
('+CONVERT(varchar(10),@pagesize)+'*('+CONVERT(varchar(10),@page)+'-1)) ArticleId from Article where
IsPass='+CONVERT(varchar(10),@isPass)+' and Publisher= ' + CONVERT(varchar(50),@publisher)+ ' order by ArticleId)
order by ArticleId'
end
end
else
begin
if(@publisher is null)
begin
set @sql='select top '+CONVERT(varchar(10),@pagesize)+' * from Article where IsPass='+CONVERT(varchar(10),@isPass)+' and
and ArticleTypeId='+CONVERT(varchar(10),@articleTypeId)+' ArticleId not in (select top
('+CONVERT(varchar(10),@pagesize)+'*('+CONVERT(varchar(10),@page)+'-1)) ArticleId from Article where
IsPass='+CONVERT(varchar(10),@isPass)+' and ArticleTypeId='+CONVERT(varchar(10),@articleTypeId)+' order by ArticleId)
order by ArticleId'
end
else
begin
set @sql='select top '+CONVERT(varchar(10),@pagesize)+' * from Article where IsPass='+CONVERT(varchar(10),@isPass)+' and
Publisher= ' + CONVERT(varchar(50),@publisher)+ ' and ArticleTypeId='+CONVERT(varchar(10),@articleTypeId)+' and ArticleId not in (select top
('+CONVERT(varchar(10),@pagesize)+'*('+CONVERT(varchar(10),@page)+'-1)) ArticleId from Article where
IsPass='+CONVERT(varchar(10),@isPass)+' and ArticleTypeId='+CONVERT(varchar(10),@articleTypeId)+' and Publisher= ' + CONVERT(varchar(50),@publisher)+ ' order by ArticleId)
order by ArticleId'
end
end
exec (@sql)
执行时
declare @totalpage int,@totalrow int
exec Select_Article_ByPass_ByArticleType_ByUser 10,1,1,1,'dsd',@totalpage output,@totalrow output
print @totalpage
print @totalrow
执行结果
消息 207，级别 16，状态 1，第 2 行
列名 'dsd' 无效。