Calling stored procedure and passing parameter from another stored procedure

Question

This is my stored procedure that deletes rows according to array that was passed to it as XML parameter:

BEGIN

DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XMLDoc

DELETE Comments WHERE 
       ID IN (SELECT * FROM OPENXML(@docHandle, '/values/value1', 2) WITH (value1 INT '.'))

EXEC sp_xml_removedocument @docHandle
END

Now instead of DELETE i want to call to another stored procedure (that executes hierarchical delete, it receives ID parameter)

How can I do that?

UPDATE: this is my second SP:

(
    @ID int
)
AS
IF @ID<>1 
BEGIN   
    CREATE TABLE #nodesC (id int primary key)
    INSERT INTO  #nodesC (id) VALUES (@ID )

    WHILE @@rowcount > 0
     INSERT INTO #nodesC 
     SELECT child.ID  
     FROM Comments child
     INNER JOIN #nodesC parent ON child.ParentId = parent.id
     WHERE child.ID  NOT IN (SELECT id FROM #nodesC)

    DELETE
    FROM Comments
    WHERE ID  IN (SELECT id FROM #nodesC)

END

Answer 1

You can't pass an array as a parameter directly. So:

• Pass in XML and parse in 2nd stored proc
• Use table datatype (SQL Server 2008)
• Multiple calls to 2nd stored proc per ID in parsed XML (bad, here for completeness)

Otherwise, the authoritative article on how do deal with this is Arrays and Lists in SQL Server 2005 by Erland Sommarskog

Answer 2

You will need a cursor.

DECLARE c CURSOR LOCAL FORWARD_ONLY FOR SELECT ... FROM OPENXML(...) WITH(...)
OPEN c
WHILE 1 <> 0 BEGIN
    FETCH c INTO ...
    IF @@fetchstatus <> 0 BREAK
    EXEC ...
END
CLOSE c
DEALLOCATE c