Running a stored procedure using a resultset as the parameter and insert into a table-CodePudding

I have the question below,

I do have a query which returns 10 rows for example.

SELECT CarId FROM Car 

EXEC spQ_GetCar (@CarId)

Also, I have a stored procedure that uses an id from the table above.

My question is, how can I run the stored procedure and use the output of the table as the parameter and then insert that into another temp table.

Would it be possible by using a cursor and dynamic SQL ?, has anyone faced this before ?

NOTES: I cannot create a table type to use it

CodePudding user response：

A fast answer:

Create a table, then you can insert-exec:

insert yourPreparedtable
EXEC spQ_GetCar (@CarId)

The true answer:

Erland Sommarskog's article is the most in-depth resource you will find. Many alternatives with detailed advantages and disadvantages.

CodePudding user response：

I achieved this by following approach below:

CREATE TABLE #TEMP (CarId INT)

DECLARE @CarId INT

DECLARE Car CURSOR
    ,CarId INT SET Car = CURSOR STATIC
FOR
SELECT CarId
FROM Car

OPEN Car

WHILE 1 = 1
BEGIN
    FETCH Car
    INTO @CarId

    IF @@FETCH_STATUS <> 0
        BREAK

    INSERT INTO #TEMP
    EXEC spQ_GetCar(@CarId)
END

CLOSE @ServiceAgreements

DEALLOCATE @ServiceAgreements

DROP TABLE #TEMP

Notes: Performance was not relevant as it was a one off script.