How to add WHERE clause after execute procedure returns values?

Let me start like this “Do we have any mechanism to add WHERE clause after execute procedure returns values?” Answer is YES but we need to spend bit time. But recently I found smart Answer for above Question.i will explain here my old concept and new concept,

Old concept,

USE AdventureWorks2008
GO
--create table
CREATE TABLE MyRecords
(col1 int,
col2 varchar(25)
);
GO

--insert Records
INSERT INTO MyRecords
SELECT TOP 15 [object_id],[name]
FROM sys.objects
WHERE (LEN([name]) BETWEEN 0 AND 25)/* cannot add less-then sign because of html validation */
ORDER BY [object_id];
GO

--create procedure
CREATE PROCEDURE proc_get_records
AS
SET NOCOUNT ON
SELECT col1 [object_id], col2 [name] FROM MyRecords;
SET NOCOUNT OFF
GO

--execute sp
EXEC proc_get_records; --this will returns 15 rows
GO

If you need to check your procedure returns values contain some specific value, then you have to do following mechanism.

--if we need to check procedure returns value contain object id 7
CREATE TABLE #tempTable
(col1 int,
col2 varchar(25)
);

INSERT INTO #tempTable
EXEC proc_get_records;

SELECT * FROM #tempTable WHERE (col1=7);
GO

New concept,

Now you can realize above method very effective but time consuming work.Let’s say your procedure contains many columns with different data types and finally it will returns more than thousand rows. Then no doubt this will be, bit time consuming processes. And sometimes your Production Database environment does not allow you to create temp tables or you might be haven’t any permission to create tables. Then this will be painful problem.This is how you can sort-out above Problem,This is very smart concept I found recently when I’m go through OPENDATASOURCE and OPENROWSET functions.And pls Notice,i`m using same table formats I have created earlier to explain this,

USE AdventureWorks2008
GO
EXEC proc_get_records;
GO
SELECT *
FROM OPENROWSET('SQLOLEDB',
'Server=THARINDU\SQLDEV2008;Trusted_Connection=yes;',
'EXEC AdventureWorks2008.dbo.proc_get_records')
WHERE ([object_id] IN(5,3,7))
ORDER BY [object_id];

Still I couldn’t found good mechanism to do same concept using oracle. Any way I’ll try and Update my blog in future post.