Luis64 Luis64 - 1 month ago 5
SQL Question

Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value

I try to run a query for every number of Zip code of an area , for example
I have n number of zip code and I have to run the query for each one, I try to do it with a variable and a select , but came with this error , I can change the zip code every time and run it, but is about 300 zip code so i will spend all day on it.

This is part of my query

----Declare Variable

Declare @Zip1 as int

Declare @Zip2 as Int

Declare @DistanceMax as numeric

Declare @DistanceMin as Numeric

set @Zip1 = (select distinct(zip_code) from zipcode
Where zip_code <>' ' )

-----@Zip1='85001'---If I do this way I have to run for every zipcode


I run my query depending of the value of @ZIP1
and its give me the error every time I run with the select clause

Any Clue?

Answer

As we said in the comments: You aren't getting a single value for @Zip1 but SQL-Server wants only one row.

Solution to that is that @Zip1 isn't an int but a table. Assumption zip_code is nvarchar:

DECLARE @Zip1 TABLE 
( 
 zipid NVARCHAR(10)
)

INSERT INTO @Zip1
    SELECT distinct zip_code from zipcode
    Where zip_code  <>' '

You can call @Zip1 just like any other Table:

SELECT zipid FROM @Zip1