Performing a query of a query on stored procedure or recommendation for best practice

Question

I have a stored procedure and its purpose is to query a table for rows that do no match the declared LocID. However, what I am trying to do now is the best method to filter out my data based on rows that:

Do not match the LocID
Do not match the ZipCode from the declared LocID value

Code:

DECLARE @LocID

SELECT ZipCode
            ,[Description] = ZipCode + ' - ' + Description
FROM LocMap
WHERE LocID <> @LocID

Here is a quick layout of table named LocMap.

LocID | ZipCode | Description
----------------------------------
 100  | 91012   | Magical Sky
 100  | 91013   | Dream Land
 101  | 91012   | Blue Ocean
 102  | 91012   | Gray Screen
 104  | 91014   | Limit Break
 108  | 91016   | Magic Hammer

The result is returned in JSON format in order to populate into the second dropdown field on a form based on the LocID the user selects from the first dropdown field. So for example, if user selects LocID = 100 from the first dropdown, then the stored procedure will execute the query and only return ZipCodes that are NOT in LocID = 100. In this case, the rows with ZipCodes 91014 and 91016 would return in the second dropdown.

What would be the best way to perform this? Would this be something I need to do through a subquery in the stored procedure? I feel like I'm over thinking this.

score 2 · Answer 1 · edited May 23 '17 at 10:33

There would be a few different ways that you could do this (store ZipCode of LocID you are querying in a variable, CTE, subquery, LEFT OUTER JOIN in query, etc), but in this case, you should really only need to check the second condition (zip <> LocID zip) since that will account for the LocID lookup (the zip of the ID you are checking will knock out that entry).

After that, you should be good with a subquery:

DECLARE @LocID INT;

SELECT
    l.[ZipCode],
    [Description] = l.ZipCode + ' - ' + l.Description
FROM LocMap l
WHERE l.ZipCode NOT IN (
    SELECT l2.ZipCode
    FROM LocMap l2
    WHERE l2.LocID = @LocID
);

Of course, run SET STATISTICS IO ON / check the actual execution plan to make sure that it performs well. You could go the CTE / join route here as well, but it would likely yield the same query plan, so I would start here. It really depends on the situation as to which is a better fit.

This looks like the optimal query to me. – Jace Sep 06 '14 at 22:26 — Jace, Sep 06 '14 at 22:26

Laurence · Answer 2 · 2014-09-06T20:50:38.733

You can do this with a correlated sub-query. Note, you don't have to explicitly exclude @LocID as it will get excluded by the second criteria too.

select
    ZipCode,
    [Description] = ZipCode + ' - ' + Description
from
    LocMap l1
where
    Not Exists (
        select
            'x'
        from
            LocMap l2
        where
            l2.LocID = @LocID and
            l2.ZipCode = l1.ZipCode
    );

Example SQLFiddle

score 0 · Answer 3 · edited May 23 '17 at 12:34

Regarding "over thinking", you don't need a stored procedure - you just need a query. IMHO, stored procedures are to be avoided wherever possible.

This query will do it:

select
  a.ZipCode,
  a.ZipCode + '-' + a.Description as Description
from LocMap a
left join LocMap b on b.locid = 100
  and a.ZipCode = b.ZipCode
where a.locid != 100
and b.ZipCode is null

This works by looking for missed joins on a matched ZipCode when joined to itself and should significantly outperform a non-join approach.

Performing a query of a query on stored procedure or recommendation for best practice

3 Answers3