Select 语句和存储过程的问题

Question

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

Answer 1

存过也可以返回表，但是要用SqlReader来一行一行的读

Answer 2

如果你有足夠的數據庫權限，試下這個Select ArticleID From OpenRowSet('sqloledb','Trusted_Connection=yes','exec 數據庫名.dbo.CA_NewsCenter_Article_GetArticle ''''')將數據庫名改為你的數據庫名稱。

Answer 3

TO zpingy(阿平) 所有的一切都在存储过程中操作  如果读出来 我早都做好了

Answer 4

可以先将存储过程返回的结果集插入到临时表中，鮁后再操作临时表
insert into # exec 存储过程

Answer 5

Select ArticleID From OpenRowSet('sqloledb','Trusted_Connection=yes','exec CompanyAssistant.dbo.CA_NewsCenter_Article_GetArticle "AND 1=1",-1') AS a谢谢  zpingy(阿平)  给我思路

Answer 6

CREATE TABLE #NewTable 
( VirtualID int  IDENTITY (1, 1)  Primary key  not NUll  ,
   OldID  int not null)
INSERT INTO #NewTable
(OldID)
  Exec('Select AfficheID From OpenRowSet(''MSDASQL'',''DRIVER={SQL Server};SERVER=SERVER;UID=sa;PWD=sa;Database=OfficeAutomation'',''exec dbo.OA_Affiche_SelectBySqlWhere " AND 1=1"  '') AS a')出错服务器: 消息 7391，级别 16，状态 1，行 1
该操作未能执行，因为 OLE DB 提供程序 'MSDASQL' 无法启动分布式事务。
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]其它会话正在使用事务的上下文。]
OLE DB 错误跟踪［OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a］。

Answer 7

tryCREATE TABLE #NewTable 
( VirtualID int  IDENTITY (1, 1)  Primary key  not NUll  ,
   OldID  int not null)
INSERT INTO #NewTable
(OldID)
Select AfficheID From OpenRowSet('MSDASQL','DRIVER={SQL Server};SERVER=SERVER;UID=sa;PWD=sa;Database=OfficeAutomation','exec dbo.OA_Affiche_SelectBySqlWhere " AND 1=1"  ') AS a
使用這個的確是有些限制。

Answer 8

如果使用以上語句還有問題的話。。既然你都使用臨時表了，可以直接這樣。CREATE TABLE #NewTable 
( VirtualID int  IDENTITY (1, 1)  Primary key  not NUll  ,
   OldID  int not null)
INSERT INTO #NewTable
(OldID) EXEC dbo.OA_Affiche_SelectBySqlWhere ' AND 1=1'

Answer 9

發現一處錯誤，你使用的居然是雙引號，改CREATE TABLE #NewTable 
( VirtualID int  IDENTITY (1, 1)  Primary key  not NUll  ,
   OldID  int not null)
INSERT INTO #NewTable
(OldID)
Select AfficheID From OpenRowSet('MSDASQL','DRIVER={SQL Server};SERVER=SERVER;UID=sa;PWD=sa;Database=OfficeAutomation','exec dbo.OA_Affiche_SelectBySqlWhere '' AND 1=1''  ') AS a

Answer 10

TO paoluo(一天到晚游泳的鱼) 
  Select AfficheID From OpenRowSet('MSDASQL','DRIVER={SQL Server};SERVER=SERVER;UID=sa;PWD=sa;Database=OfficeAutomation','exec dbo.OA_Affiche_SelectBySqlWhere '' AND 1=1''  ') AS a中 AfficheID  是参数传进来得值  OA_Affiche_SelectBySqlWhere 也是传进来的值