10

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'
Tim Post
  • 33,371
  • 15
  • 110
  • 174
TheTechGuy
  • 16,560
  • 16
  • 115
  • 136
  • 1
    possible duplicate of [Select columns from result set of stored procedure](http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure) – Alex K. Feb 21 '12 at 13:39
  • I always use google site:stackoverflow.com select+from+stored+procedure – Alex K. Feb 21 '12 at 14:51

5 Answers5

17

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'
TheTechGuy
  • 16,560
  • 16
  • 115
  • 136
Lamak
  • 69,480
  • 12
  • 108
  • 116
1

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.

UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51
1

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':

EXEC xp_readerrorlog 0, 1, 'fail';
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Right I posted just an example, I will be searching on other parameters. Your query can be helpful as well. – TheTechGuy Feb 21 '12 at 14:03
0

You can copy output from sp to temporaty table.

insert into #temp
EXEC xp_readerrorlog

and then use where clause with the temp table

Vikram
  • 8,235
  • 33
  • 47
0

or you can make a Table-valued Function

Zyku
  • 1,429
  • 2
  • 23
  • 37