HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Similar statements need duplication removed

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
removedneedstatementsduplicationsimilar

Problem

How would you refactor this?

ExpensiveQuery must only run when necessary.

DECLARE @city VARCHAR(10)
DECLARE @status INT

IF @status = 5
BEGIN
    SELECT c.Name, c.Phone
    FROM Customers c
    WHERE c.City = @city
    AND c.Status = 5
END
ELSE IF @status = 10
BEGIN
    SELECT c.Name, c.Phone
    FROM Customers c
    WHERE c.City = @city
    AND c.Status = 10
    AND c.SignUpDate NOT IN (EXEC ExpensiveQuery)
END

Solution

I am not sure your quest for de-duplication is warranted,
the only approach I know of is this:

DECLARE @city VARCHAR(10)
DECLARE @status INT

SELECT c.Name, c.Phone
FROM Customers c
WHERE c.City = @city
AND c.Status = @status
AND ( ( c.Status = 5 )
OR    ( c.Status = 10  AND c.SignUpDate NOT IN (EXEC ExpensiveQuery) ) )

Code Snippets

DECLARE @city VARCHAR(10)
DECLARE @status INT

SELECT c.Name, c.Phone
FROM Customers c
WHERE c.City = @city
AND c.Status = @status
AND ( ( c.Status = 5 )
OR    ( c.Status = 10  AND c.SignUpDate NOT IN (EXEC ExpensiveQuery) ) )

Context

StackExchange Code Review Q#61100, answer score: 5

Revisions (0)

No revisions yet.