Hi,

I have a stored procedure that has to extract the child records for
particular parent records.

The issue is that in some cases I do not want to extract all the child
records only a certain number of them.

Firstly I identify all the parent records that have the requird number
of child records and insert them into the result table.

insert into t_AuditQualifiedNumberExtractDetails
    (BatchNumber,
    EntryRecordID,
    LN,
    AdditionalQualCritPassed)
(select t1.BatchNumber,
       t1.EntryRecordID,
       t1.LN,
       t1.AdditionalQualCritPassed
from
(select BatchNumber,
      RecordType,
      EntryRecordID,
      LN,
      AdditionalQualCritPassed
from t_AuditQualifiedNumberExtractDetails_Temp) as t1
inner join
(select BatchNumber,
    RecordType,
    EntryRecordID,
    Count(*) as AssignedNumbers,
    max(TotalNumbers) as TotalNumbers
from t_AuditQualifiedNumberExtractDetails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) = max(TotalNumbers)) as t2
on t1.BatchNumber = t2.BatchNumber
and t1.RecordType = t2.RecordType
and t1.EntryRecordID = t2.EntryRecordID)

then insert the remaining records into a temp table where the number of
records required does not equal the total number of child records, and
thenloop through each record manipulating the ROWNUMBER to only select
the number of child records needed.

insert into @t_QualificationMismatchedAllocs
        ([BatchNumber],
        [RecordType],
        [EntryRecordID],
        [AssignedNumbers],
        [TotalNumbers])
    (select BatchNumber,
          RecordType,
              EntryRecordID,
              Count(*) as AssignedNumbers,
              max(TotalNumbers) as TotalNumbers
    from t_AuditQualifiedNumberExtractDetails_Temp
    group by BatchNumber, RecordType, EntryRecordID
    having count(*) <> max(TotalNumbers))

    SELECT @QualificationMismatched_RowCnt = 1

    SELECT @MaxQualificationMismatched = (select count(*) from
@t_QualificationMismatchedAllocs)

while @QualificationMismatched_RowCnt <= @MaxQualificationMismatched
    begin
    --## Get Prize Draw to extract numbers for
        select     @RecordType  = RecordType,
            @EntryRecordID = EntryRecordID,
            @AssignedNumbers = AssignedNumbers,
            @TotalNumbers = TotalNumbers
        from @t_QualificationMismatchedAllocs
        where QualMismatchedAllocsRowNum = @QualificationMismatched_RowCnt

            SET ROWCOUNT @TotalNumbers

        insert into t_AuditQualifiedNumberExtractDetails
                       (BatchNumber,
            EntryRecordID,
            LN,
            AdditionalQualCritPassed)
            (select BatchNumber,
               EntryRecordID,
               LN,
               AdditionalQualCritPassed
        from t_AuditQualifiedNumberExtractDetails_Temp
        where RecordType = @RecordType
        and EntryRecordID = @EntryRecordID)

        SET @QualificationMismatched_RowCnt =

QualificationMismatched_RowCnt + 1
        SET ROWCOUNT 0
    end

Is there a better methodology for doing this .....

Is the use of a table variable here incorrect ?

Should I be using a temporary table or indexed table if there are a
large number of parent records where the child records required does
not match the total number of child records ?