question

Swpanil, You can insert the result of the stored procedure set into a temp table or table variable then you can do further processing on that temp tables

e.g Insert into #temp
    EXEC MyProcedure

    SELECT * FROM #temp WHERE StringData Lik '%SQL%'

    

Thanks for answer ... But what I exactly want, here I am describing currently I am using views for desired output : CREATE PROCEDURE [dbo].[USP_GetPrintData] @ViewName VARCHAR(1000), @Res VARCHAR(5000) AS DECLARE @sql nvarchar(max) BEGIN SET @sql ='SELECT * FROM '+ @ViewName + ' where ' + @Res execute sp_executesql @sql END This sp I am using.. @ViewName which is View Name and @Res which is string expression contains string like "Column1=1 AND Column2 Like '%SQL%' " Now what I want exactly instead of using View I want to use resultset of stored procedure.And on that resultset I want to apply 'WHERE'+ string expression...

Cyborg, The thing is like that declaring temparory table we need to define the columns and we are not aware about which columns stored procedure going to return. I need to send stored procedure name and search expression which gets attached to WHERE clause but I have sureity that search expression which i am sending from front end contains only those columns which will stored procedure result set return.....

So try using OPENROWQUERY

SELECT * 
INTO #temp --(or @tempTable)
FROM OPENQUERY(, 'EXEC Procedure')

then apply search criteria on it.

SELECT * From #temp  -- (or @tempTable)
WHERE 

But the concern is the performance while dealing with huge data. Imagine your SP is returning 5 million records and your search criteria limits it to thousands records, then its really a waste time and resources. So you may need to work out different plan, something like preferring a Table-valued function over the existing Procedure.

Could not find server 'ITMS' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. this error I m getting I am unable to put linked server in OPENQUERY..How to do this??

Swapnil, to use OPENQUERY you should create your server as a linked server, so better try using OPENROWSET

SELECT *
INTO #temp
FROM OPENROWSET('SQLNCLI', 'Server=
  
   ;Trusted_Connection=yes;',
     'EXEC 
   
    .
    
     .
     
      )') AS a;

     
    
   
  

for more details regarding OPERNROWSET refer [here][1] [1]: http://msdn.microsoft.com/en-us/library/ms190312.aspx