Dependent resultsets in a stored procedure

[bagpiper]

Hi there,

I'd need to generate multiple recordsets within a MS SQL stored procedure. The thing is these recordsets depend on each previous one (the first one obviously has parameters). How can I make this work? I can't create views with SPs. Thank you very, very much.

Martin

[andi_g69]

You can check out OPENROWSET method in BOL to execute a SP in a query and use the resultset in a from clause of another query.

Other options are temp tables or cursurs but the appropriate method depends on the actual problem

[bagpiper]

Hi andi_g69,

Thanks for your feedback. I thought I'd explain the actual problem I'm dealing with.

I've got to perform a query on a table that has 2 million records. Then, I need to query the results of this query and subsequent ones a couple of times to get the data I need.

I was thinking of creating temp tables but I'm not familiar what would happen when I have several users performing the same query, nor what the best-practice would be for this case.

The other important issue I found is that a simple select statement (with two conditions) on this one table already takes too long. May be the proper way to fix this whole thing is to figure out how to speed up this first query. Is there anything I can do to even speed this up?

Thank you for your kind assistance,

Martin

[andi_g69]

For use of temporary tables look up CREATE TABLE in BOL. There is a section about SQL temp tables.

RE: Speeding up the initial query

You should make sure your table is indexed properly.
You say it holds about 2 million records but your query is obviously using only a subset. It might be an option to archive some of the records into another table?

[bagpiper]

Hi andy_g69,

That is correct! I had to create indexes for the table I was working with...and that fixed the issue. I also had to create a new table with "processed" data, where I'm clustering a column index.

Thanks again!

Martin