How to do a SELECT on SP Result
Hi there,
Problem:
Having an old webapplication with dynamic build sql queries. Now the database changed completly because of new db model. Queries are getting much more complex now, so i put them into stored procedures.
Now i want to work with the data in an application for sorting an filtering. All the 'WHERE' conditions are build dynamic within the code. For not modifying the whole application i would like to do something like:
SELECT * FROM (EXECUTE sp_someprocedure) WHERE X = 1
Is there a way to realize this ?
Thx for helping
Hi,
Just doing queries won′t work, although there is a option to do that (via OPENQUERY) its not the prefered way of calling stored procedures. It would be better to use a stored procedure calling the procedure and inserting the data in a temp table andf later select on this table using your condition.
Something like:
INSERT INTO #TempTable
EXEC sp_Yourprocedure
SELECT * FROM #TempTable
where youcondition = true
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
You may want to consider changing sp_comeprocedure to a table valued function (tvf_somefunction), assuming you always want to call it inside a select statement. Then your code ends up looking like:
select * from tvf_somefunction() where x = 1
