我可以搜索存储过程结果吗？

Let's say I have a stored procedure which returns a large set of data. Can I write another query to filter the result of stored procedure?

假设我有一个存储过程，它返回一大组数据。我可以编写另一个查询来过滤存储过程的结果吗？

For example:

例如：

select * from
EXEC xp_readerrorlog
where LogDate = '2011-02-15'

5 个解决方案

#1

You would need to first insert the results of the stored procedure on a table, and then query those results.

您需要先在表上插入存储过程的结果，然后查询这些结果。

create table #result (LogDate datetime, ProcessInfo varchar(20),Text text)

INSERT INTO #Result
EXEC xp_readerrorlog

SELECT *
FROM #Result
WHERE datepart(yy,LogDate) = '2012'

#2

Does returning the error log for just an entire day make the result any more useful? I think it will still be full of useless entries. If you're looking for specific events, why not use one of the filter parameters for xp_readerrorlog? The following wil return all rows in the current log that contain the string 'fail':

将错误日志返回一整天会使结果更有用吗？我认为它仍然会充满无用的条目。如果您正在寻找特定事件，为什么不使用xp_readerrorlog的一个过滤器参数？以下wil将返回当前日志中包含字符串'fail'的所有行：

EXEC xp_readerrorlog 0, 1, 'fail';

#3

You can't make it part of a query, BUT you could insert the resulting data into a temp table or table variable and then use that for your query.

您无法将其作为查询的一部分，但您可以将结果数据插入临时表或表变量，然后将其用于查询。

#4

You can copy output from sp to temporaty table.

您可以将sp的输出复制到temporaty表。

insert into #temp
EXEC xp_readerrorlog

and then use where clause with the temp table

然后在临时表中使用where子句

#5

or you can make a Table-valued Function

或者您可以创建一个表值函数

#1