CODEWITHSUNDEEP
stored-proceduresselect
Lieven Cardoen
Lieven Cardoen

Reputation: 25959

Stored Procedure Select as Argument

Is the following possible:

EXEC sp_Edu3_DeleteTreeStructure (SELECT TreeStructureId FROM TreeStructures)

The SP normally takes one argument. What I want is that the SP is executed for each TreeStructureId found by the Query.

thx, Lieven Cardoen

Upvotes: 2

Views: 469

Answers (1)

Robin Day
Robin Day

Reputation: 102478

You can use a CURSOR to do this.

DECLARE @treeStructureId int

DECLARE TreeStructureCursor CURSOR FOR
    SELECT
        TreeStructureId
    FROM
        TreeStructures

OPEN TreeStructureCursor

FETCH NEXT FROM TreeStructureCursor
INTO @treeStructureId

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_Edu3_DeleteTreeStructure(@treeStructureId)

    FETCH NEXT FROM TreeStructureCursor
    INTO @treeStructureId
END

CLOSE TreeStructureCursor
DEALLOCATE TreeStructureCursor

Upvotes: 2

Related Questions