Using a select Statement (with multiple results) as an input parameter for Stored Procedure

I have two tables. Episodes, and Assessments. Each Episode is made up of multiple Assessments.

SQL Server 2005.

I have a fully functioning DeleteAssessment Stored procedure that accepts the AssessmentID as an input parameter and deletes it. I'm trying to setup a DeleteEpisode assessment that, before deleting the Episode, calls the DeleteAssessment Stored procedure to delete all the related a开发者_如何转开发ssessments. I believe this code further explains what I want to do..

EXEC DeleteAssessment (SELECT AssessmentID FROM Assessments WHERE EpisodeID = @EpisodeID)

The select statement as the parameter will return multiple assessmentID's which I then need to pass to the DeleteAssessment Stored Procedure.

Am I going about this in the wrong way? Is there something I'm missing?

If you are using SQL Server 2008 the correct way is to use a TVP to pass in the relevant table as a parameter.

Yeah, that's not going to work. If DeleteAssessment is complicated enough that you can't just delete the Assessments related to an episode, you need to do something like this in DeleteEpisode:

DECLARE @AssessmentID INT;

CREATE TABLE #AssessmentIDs (AssessmentID INT);

INSERT #AssessmentIDs (AssessmentID)

SELECT AssessmentID FROM Assessments WHERE EpisodeID = @EpisodeID;

WHILE (SELECT COUNT(*) FROM #AssessmentIDs WHERE AssesmnentID = @AssessmentID) > 0
BEGIN
    SELECT @AssessmentID = TOP 1 AssessmentID FROM #AssessmentIDs

    EXEC DeleteAssessment @AssessmentID

    DELETE #AssessmentIDs WHERE AssessmentID = @AssessmentID
END;