Posted by Catch_22 on 10/29/06 06:26

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 ?

[Back to original message]