CODEWITHSUNDEEP
sqlsql-serverredundancy
P.Petkov
P.Petkov

Reputation: 1579

How to remove redundant code from SQL conditional query

I have the following case.

I got table with some verified, non-verified records and (let say) master verified records. Edited: In the table I got standard unique ID field, but there is also a column called VerifiedRecordID that point to other record in the table or it is NULL.

If record has VerifiedRecordID = NULL, it is non-verified one.

If record has VerifiedRecordID = some_id, then this record is verified one and

the record that ID=some_id is master verified one.

Many verified records may point to one master verified record. The last one are there just to provide verified records with verified data and cannot be access directly.

But there is also another factor to determine whether the record is verified or not, not only this field, so verification is determined by a stored procedure. So, if you access the record that is not verified, it will return its fields. If you access the record that IS verified it will ignore its fields (except ID) and return the fields of the master verified record (except ID) it points to.

So, I want to create a store procedure that extract records from the table by passed @ids parameter, which may contain ids of verified or non-verified records (but NOT master verified records, because they are not accessible directly). I got this one:

DECLARE @verifiedMasterRecordsIds [dbo].[Ints]
INSERT INTO @verifiedMasterRecordsIds 
EXEC sp_getVerifiedMasterRecordsIds

SELECT record.ID, 
    CASE 
        WHEN record.ID IN (SELECT ID FROM @verifiedMasterRecordsIds) 
        THEN verRecord.Field1
        ELSE record.Field1
    END AS Field1,
    CASE 
        WHEN record.ID IN (SELECT ID FROM @verifiedMasterRecordsIds) 
        THEN verRecord.Field2
        ELSE record.Field2
    END as Field2,
    CASE 
        WHEN record.ID IN (SELECT ID FROM @verifiedMasterRecordsIds) 
        THEN verRecord.Field3
        ELSE record.Field3
    END as Field3,
    CASE 
        WHEN record.ID IN (SELECT ID FROM @verifiedMasterRecordsIds) 
        THEN verRecord.Field4
        ELSE record.Field4
    END AS Field4
INTO #TempRecords
FROM Records as record
LEFT JOIN Records as verRecord ON record.VerifiedRecordID=verRecord.ID

WHERE record.ID IN (SELECT ID FROM @ids)

My first and most obvious concern is the redundant code (SELECT ID FROM @verifiedMasterRecordsIds) in each select condition. How can this be refactored to avoid the redundancy? Also, if there is a better approach to extract data from this table with this logic, I'll be glad to see it.

Upvotes: 0

Views: 194

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I find the logic a bit hard to follow with out examples -- all those lists of ids, it is not quite clear which you really need to match to choose from one table or the other.

That said, your query would be simpler if you unpivoted the values and moved the table logic to the FROM clause. This might do what you want:

SELECT r.ID, v.which,
       COALESCE(v.verified_field, r._recordfield)
INTO #TempRecords
FROM Records r LEFT JOIN
     @verifiedMasterRecordsIds vmr
     ON vmr.id = r.id LEFT JOIN
     Records vr
     ON vr.ID = r.VerifiedRecordID AND vmr.id IS NOT NULL 
     (VALUES ('Field1', r.Field1, vr.Field1),
             ('Field2', r.Field2, vr.Field2),
             ('Field3', r.Field3, vr.Field3),
             ('Field4', r.Field4, vr.Field4)
     ) v(which, record_field, verified_field)
WHERE r.ID IN (SELECT i.ID FROM @ids i) ;

You could, of course, repivot the data.

Actually, perhaps you just want to move the condition to the FROM clause -- and unpivoting is not necessary:

SELECT r.ID, v.which,
       (CASE WHEN vrm.id IS NOT NULL THEN vr.Field1 ELSE r.Field1 END),
       (CASE WHEN vrm.id IS NOT NULL THEN vr.Field2 ELSE r.Field2 END),
       (CASE WHEN vrm.id IS NOT NULL THEN vr.Field3 ELSE r.Field3 END),
       (CASE WHEN vrm.id IS NOT NULL THEN vr.Field4 ELSE r.Field4 END)
INTO #TempRecords
FROM Records r LEFT JOIN
     @verifiedMasterRecordsIds vmr
     ON vmr.id = r.id LEFT JOIN
     Records vr
     ON vr.ID = r.VerifiedRecordID  
WHERE r.ID IN (SELECT i.ID FROM @ids i) ;

Upvotes: 0

Vad1m
Vad1m

Reputation: 431

This code returns only records contained in @ids variable. And selects fields relying on existing records in @verifiedMasterRecordsIds variable. Please write whether I understood you correctly

DECLARE @verifiedMasterRecordsIds [dbo].[Ints]
INSERT INTO @verifiedMasterRecordsIds 
EXEC sp_getVerifiedMasterRecordsIds
;

SELECT record.ID, verRecord.Field1, verRecord.Field2, verRecord.Field3, verRecord.Field4
FROM Records as record
LEFT JOIN Records as verRecord ON record.VerifiedRecordID=verRecord.ID
WHERE record.ID IN (SELECT ID FROM @ids) 
and record.ID IN (SELECT ID FROM @verifiedMasterRecordsIds)

union 

SELECT ID, Field1, Field2, Field3, Field4
FROM Records as record
WHERE ID IN (SELECT ID FROM @ids) 
and ID NOT IN (SELECT ID FROM @verifiedMasterRecordsIds);

Upvotes: 1

Related Questions