存储过程能创建，但执行显示列名无效的问题．．．
存储过程创建如下
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 IsPass=@isPass
end
else
begin
select @totalrow=COUNT(*) from Article where IsPass=@isPass and Publisher=@publisher
end
end
else
begin
if(@publisher is null)
begin
select @totalrow=COUNT(*) from Article where IsPass=@isPass and ArticleTypeId=@articleTypeId
end
else
begin
select @totalrow=COUNT(*) from Article where IsPass=@isPass and ArticleTypeId=@articleTypeId and Publisher=@publisher
end
end
set @totalpage=@totalrow/@pagesize
if(@totalrow%@pagesize>0)
begin
set @totalpage=@totalpage+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' 无效。
消息 207，级别 16，状态 1，第 3 行
列名 'dsd' 无效。
应该是创建存储过程的语法出了问题，请各位帮忙看看，谢谢．．．
[解决办法]
- SQL code
alter proc Select_Article_ByPass_ByArticleType_ByUser@pagesize int,@page int,@articleTypeId int,@isPass int,@publisher nvarchar(50),@totalpage int output,@totalrow int outputasdeclare @sql varchar(2000)if(@articleTypeId=0)begin if(@publisher is null) begin select @totalrow=COUNT(*) from Article where IsPass=@isPass end else begin select @totalrow=COUNT(*) from Article where IsPass=@isPass and Publisher=@publisher end endelsebegin if(@publisher is null) begin select @totalrow=COUNT(*) from Article where IsPass=@isPass and ArticleTypeId=@articleTypeId end else begin select @totalrow=COUNT(*) from Article where IsPass=@isPass and ArticleTypeId=@articleTypeId and Publisher=@publisher end endset @totalpage=@totalrow/@pagesizeif(@totalrow%@pagesize>0)beginset @totalpage=@totalpage+1endif(@articleTypeId=0)begin if(@publisher is null) begin set @sql='select top '+CONVERT(varchar(10),@pagesize)+' * from Article where IsPass='+CONVERT(varchar(10),@isPass)+' andArticleId 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)+' andPublisher= ''' + 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' endendelsebeginif(@publisher is null) begin set @sql='select top '+CONVERT(varchar(10),@pagesize)+' * from Article where IsPass='+CONVERT(varchar(10),@isPass)+' andand 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)+' andPublisher= ''' + 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' endendexec (@sql)
[解决办法]