Subquery returned more than 1 value.how to handle this and get multiple ids

Question

SET @IdInternalSelection = (
    SELECT iss.IdInternalSelection 
    FROM HesSelection.InternalSelection iss 
    INNER JOIN HesSelection.ExtractedLabInternalSelection2CatPatientActivity cpa 
       ON iss.IdInternalSelection = cpa.IdInternalSelection               
    WHERE cpa.IdCatPatientActivity = @IdCatPatientActivity)

I will get output of IdCatPatientActivity from another query.

Based on this IdCatPatientActivity, I need to set IdInternalSelection which fails for some IdCatPatientActivity ids.

This is because IdCatPatientActivity is some cases is linked with multiple IdInternalSelection

What i need is to get all the IdInternalSelection associated with IdCatPatientActivity and then loop each IdInternalSelection and insert it into different table.

I am not getting multiple IdInternalSelection ids. Can some one please help me?

Answer 1

You need the variable is of type TABLE.

declare @IdInternalSelection  table
(
    Id varchar(1000)
)

insert into @IdInternalSelection
SELECT iss.IdInternalSelection 
                            FROM HesSelection.InternalSelection iss 
                            INNER JOIN HesSelection.ExtractedLabInternalSelection2CatPatientActivity cpa ON iss.IdInternalSelection = cpa.IdInternalSelection                 
                            WHERE cpa.IdCatPatientActivity = @IdCatPatientActivity