Select 语句和存储过程的问题
select ArticleID From (select * from dbo.CA_NewsCenter_Article) As b
结果
ArticleID
22
24
25
30
31
select ArticleID From (Exec dbo.CA_NewsCenter_Article_GetArticle ' ') As b
服务器: 消息 156，级别 15，状态 1，行 1
在关键字 'Exec ' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: ') ' 附近有语法错误。
存储过程
CREATE PROCEDURE CA_NewsCenter_Article_GetArticle
@Getwhere varchar(5000)= ' ',
@Selectwhere int
AS
BEGIN
if @Selectwhere=-1
begin
exec ( 'select [ArticleID],
CA_NewsCenter_Article.ChannelID as ChannelID,
CA_NewsCenter_Article.ClassID as ClassID,
[BlogID],
[RoleGroupID],
[RoleGroupName],
[Title],
[CustomTitle],
[Keyword],
[KeywordID],
[Author],
[AuthorID],
[CopyFrom],
[Hits],
[IsOnTop],
[IsElite],
[IsPop],
[IsDeleted],
[Rank],
[Status],
[Content],
[UploadFilesUrl],
[ThumbnailUrl],
[InfoPoint],
[BonusAdd],
CA_NewsCenter_Article.ChargeType as ChargeType,
CA_NewsCenter_Article.PitchTime as PitchTime,
[ReadTimes],
CA_NewsCenter_Article.EnableComment as EnableComment,
[EnableCommentLink],
[EnableErrorReport],
[EnableFontChange],
[CommentCount],
[ErrorCount],
[DividePercent],
CA_NewsCenter_Article.CreatorID as CreatorID,
CA_NewsCenter_Article.Creator as Creator,
CA_NewsCenter_Article.CreateTime as CreateTime,
CA_NewsCenter_Article.UpdaterID as UpdaterID,
CA_NewsCenter_Article.Updater as Updater,
CA_NewsCenter_Article.UpdateTime as UpdateTime,
CA_NewsCenter_Article.TemplateID as TemplateID,
[SkinID],
[Summary],
[IncludePic],
[PaginationType],
[MaxCharPerPage],
[IsReceive],
[ReceiveUser],
[Received],
[AutoReceiveTime],
[ReceiveType],
[Copymoney],
[IsPayed],
[Beneficiary],
[PayDate],
[Subheading],
[SubjectID],
[LinkUrl],
[Assessor],
[AssessorID],
[AssessorTime],
[ClassName]
from CA_NewsCenter_Article,CA_Global_Class where
CA_NewsCenter_Article.ClassID=CA_Global_Class.ClassID
and
1=1 '+@Getwhere)
end
else
begin
exec ( 'select top '+@Selectwhere+ ' [ArticleID],
CA_NewsCenter_Article.ChannelID as ChannelID,
CA_NewsCenter_Article.ClassID as ClassID,
[BlogID],
[RoleGroupID],
[RoleGroupName],
[Title],
[CustomTitle],
[Keyword],
[KeywordID],
[Author],
[AuthorID],
[CopyFrom],
[Hits],
[IsOnTop],
[IsElite],
[IsPop],
[IsDeleted],
[Rank],
[Status],
[Content],
[UploadFilesUrl],
[ThumbnailUrl],
[InfoPoint],
[BonusAdd],
CA_NewsCenter_Article.ChargeType as ChargeType,
CA_NewsCenter_Article.PitchTime as PitchTime,
[ReadTimes],
CA_NewsCenter_Article.EnableComment as EnableComment,
[EnableCommentLink],
[EnableErrorReport],
[EnableFontChange],
[CommentCount],
[ErrorCount],
[DividePercent],
CA_NewsCenter_Article.CreatorID as CreatorID,
CA_NewsCenter_Article.Creator as Creator,
CA_NewsCenter_Article.CreateTime as CreateTime,
CA_NewsCenter_Article.UpdaterID as UpdaterID,
CA_NewsCenter_Article.Updater as Updater,
CA_NewsCenter_Article.UpdateTime as UpdateTime,
CA_NewsCenter_Article.TemplateID as TemplateID,
[SkinID],
[Summary],
[IncludePic],
[PaginationType],
[MaxCharPerPage],
[IsReceive],
[ReceiveUser],
[Received],
[AutoReceiveTime],
[ReceiveType],
[Copymoney],
[IsPayed],
[Beneficiary],
[PayDate],
[Subheading],
[SubjectID],
[LinkUrl],
[Assessor],
[AssessorID],
[AssessorTime],
[ClassName]
from CA_NewsCenter_Article,CA_Global_Class where
CA_NewsCenter_Article.ClassID=CA_Global_Class.ClassID
and
1=1 '+@Getwhere )
end
END
GO
[解决办法]
返回结果的存储过程不能做为一个数据表来使用。
[解决办法]
只有函数返回的数据可以做为表，存储过程不行，因为存储过程没有返回类型。。。
[解决办法]
存过也可以返回表，但是要用SqlReader来一行一行的读
[解决办法]
如果你有足夠的數據庫權限，試下這個
Select ArticleID From OpenRowSet( 'sqloledb ', 'Trusted_Connection=yes ', 'exec 數據庫名.dbo.CA_NewsCenter_Article_GetArticle ' ' ' ' ')
將數據庫名改為你的數據庫名稱。
[解决办法]
可以先将存储过程返回的结果集插入到临时表中，鮁后再操作临时表
insert into # exec 存储过程
[解决办法]
可以不用加AS。 ：)
