Can you use a stored procedures with dynamic sql?

[Jocsta]

Is it possible to call a stored procedure, and use it's output within a dynamic sql statement.

Background :- My requirements group have gone hierarchy crazy. In the Oracle implementation we are using connect by, but as SQLServer has no such thing, I have to use a stored procedure to calculate the hierarchy to search, then build the results of this call into another query.

All search queries are dynamically constructed in the application, and may or may not involve a hierarchical search element(s), so I cant do the whole thing in a single stored procedure. So I was wondering if there's a way to combine the two into a single query, i.e. somehow using the output of a stored procedure in an IN block in the where clause or a subquery? ( I've tried, I've failed, and think I'm probably wishing for too much with this one!! )

JOC.

 

[AWithers]

One way around it is to as part of procedure2 use procedure1 to populate a temporary table then use the contents of the temporary table in the subquery,

e.g.

Create table #tmp (<column definitions>)

Insert #tmp
Exec procedure1


.....


where yourfield in(select yourcolumn from #tmp)


Andy