Am I forced to use a cursor here?

Using SQL Server 2000, got to get a complex stored procedure to work

[bhonda]

Ok guys and gals, here's my problem. I'm finding it really hard to explain, so please bear with me!

I'm using SQL Server 2000. I've got 2 stored procedures. Let's call them SP1 and SP2. SP1 is the control procedure, that has no parameters, it is just called. SP2 is the worker procedure, that accepts a parameter and returns a result. The idea is that SP1 looks up data from the database, and performs SP2 on each row found. SP1 then returns all this data.

Does that make any kind of sense?

Basically, I've got it working, but with a cursor. I create a local temporary table, and on each iteration of the cursor, the second stored procedure is called, and the result added to the temporary table.

While this works, I now need to extend it. This would result in a nested cursor, which I kinda want to avoid. Is there any way that I can do what I explained above, but without a cursor? Is there any way I can create an INSERT statement that calls another stored procedure as one of it's parameters...for example...

INSERT INTO table1 (result) VALUES (exec SP2 param1, param2)

...or something like that?

I'm really sorry if this makes no sense...I'm pretty rusty on the old SQL!

Cheers - any help or advice will be greatly appreciated!

B

[LifeinAsia]

Does SP2 absolutely need to be a stored procedure or can you make it a User Defined Function (UDF)?

I have never tried putting a SP in an INSERT statement, but UDFs certainly work. UDFs only return scalar values or a table, so it may not work for your situation.

[syber]

You were close -

INSERT INTO table1
EXEC SP2 param1, param2

(you need to make sure that the columns that come back from SP2 are in the same order as the columns in table1 - or use a column list for the INSERT)

Art

[bhonda]

Thanks guys,

The UDF did it for me. Thinking about it, that was what I should have done in the first place. It now works like a dream!

Cheers,

B