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

|||You get an invalid object error when you try to insert into on a Temp Table. This does not work. Any other suggestions?