CODEWITHSUNDEEP
sql-serversql-server-2005
Jay
Jay

Reputation: 1

SQL server Query help

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.

Upvotes: 0

Views: 117

Answers (4)

strattonn
strattonn

Reputation: 2012

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

Upvotes: 0

chaosTechnician
chaosTechnician

Reputation: 1710

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
)

Upvotes: 2

Rubens Farias
Rubens Farias

Reputation: 57996

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

Upvotes: 0

JeffO
JeffO

Reputation: 8053

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/

Upvotes: 0

Related Questions