question

Swapnil Mahajan avatar image
Swapnil Mahajan asked

Applying Where clause to stored procedure result

I am generating where clause from front end I want to apply it to some stored procedures result set. How I can?? I am usign sql server 2008.
sql-server-2008sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Cyborg avatar image
Cyborg answered
you can do this by using openquery SELECT * FROM OPENQUERY(, 'EXEC Procedure') WHERE Condition but you may have performance issues with openquery. My recommendation is to use table valued function instead of procedure so that you can use SELECT with where clause. Another option is to pass the filter condition as a parameter to your stored procedure and build dynamic query with it inside your Stored Procedure.
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image Cyborg commented ·
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%'

    
1 Like 1 ·
Swapnil Mahajan avatar image Swapnil Mahajan commented ·
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...
0 Likes 0 ·
Swapnil Mahajan avatar image Swapnil Mahajan commented ·
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.....
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
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.
0 Likes 0 ·
Swapnil Mahajan avatar image Swapnil Mahajan commented ·
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??
0 Likes 0 ·
Cyborg avatar image Cyborg commented ·
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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.