SQL server Query help

Question

I am passing a list of zipcodes to a stored proc (say 11111, 11112, 11113, 11114, 11115) and the zip code table contains only few rows (say 11111, 11112, 11113) and now I want to write a query which returns the zip codes that are not exists in the table (11114 and 11115)

Could anyone please help me to write a query like this?

Thanks in advance.

Answer 1

You have use a JOIN to build your first query.

Now you'll need a RIGHT JOIN/WHERE to include all items in your zip list which correspondent record on zip table is null

EDIT: Here is a sample

CREATE TABLE ZipCodesTable(Code int NOT NULL)
CREATE TABLE ZipCodesList (Code int NOT NULL)

INSERT INTO ZipCodesTable (Code) VALUES (11111)
INSERT INTO ZipCodesTable (Code) VALUES (11112)
INSERT INTO ZipCodesTable (Code) VALUES (11113)

INSERT INTO ZipCodesList  (Code) VALUES (11111)
INSERT INTO ZipCodesList  (Code) VALUES (11112)
INSERT INTO ZipCodesList  (Code) VALUES (11113)
INSERT INTO ZipCodesList  (Code) VALUES (11114)
INSERT INTO ZipCodesList  (Code) VALUES (11115)

SELECT ZipCodesList.Code
  FROM ZipCodesTable RIGHT
  JOIN ZipCodesList
 ON ZipCodesTable.Code = ZipCodesList.Code
 WHERE ZipCodesTable.Code IS NULL

Answer 2

This article uses CTE to take your string of entries, convert to a table so you can include in a query: http://www.sqlservercentral.com/articles/CTE/67974/

Answer 3

Not knowing your exact implementation, this quick and dirty query should be adaptable to what you're trying. Select the zip codes from the list you passed which aren't in the full table of zip codes:

SELECT ZipCode 
FROM PassedList 
WHERE ZipCode NOT IN (
  SELECT DISTINCT ZipCode
  FROM ZipCodeTable
)

Answer 4

No need to create a table in SQL use a temp table in the stored proc. If you need to pass in the zips as string use this. If you have a table valued parameter you can pass them in as a table.

    CREATE PROCEDURE spCheckZip
@Zips varchar(256)  --space delimited list of zips

AS

IF OBJECT_ID('tempdb..#ZipCheck') IS NOT NULL 
 DROP TABLE #ZipCheck
CREATE TABLE #ZipCheck (zipcode string) --Note string not int for zips with leading 0's.

IF CharIndex(' ',@Zips) > 1
BEGIN

   Declare @StartPos as int
   Declare @ZipCd As Varchar(16)

   set @StartPos = 2
   set  @ZipCd = substring(@Zips,1,CharIndex(',',@Zips))
   WHILE @StartPos > 1 and @StartPos < Len(@Zips)
   BEGIN
   INSERT Into #ZipCheck (zipcode) 
   SELECT Substring(@ZipCd,1,len(@ZipCd)-1)
   SET @StartPos = charindex(',',@Zips, @StartPos)+1
   SET @ZipCd = substring(@Zips,@StartPos,CharIndex(',',@Zips))
   END
END

SELECT ZipCode 
FROM ZipCheck LEFT JOIN YourZipTable on ZipCheck.zipcode=YourZipTable.YourZipField
WHERE YourZipTable.YourZipField IS NULL