CODEWITHSUNDEEP
mysqlsql
user2528985
user2528985

Reputation: 71

Where Clause matching results from function

I have a routine which returns a list of zip codes within so many miles of a particular zip code. I can call the routine and get the result list:

CALL Location.GetNearByZipCodes(28078,5);

Result Set:

28031,28070,28078,28205

I would like to do a query where it selects all records with the output from the routine as part of the where clause:

select * from Location where zipcode in (Location.GetNearByZipCodes(28078,5));

However, this does not work. Is it possible to use the results from the function as part of the where clause? If so, what is the correct syntax?

The above select query fails with:

Error Code 1305: Location.GetNearByZipCodes does not exist.

Thanks in advance for your help!

Upvotes: 0

Views: 53

Answers (1)

Yotam Omer
Yotam Omer

Reputation: 15366

Try this:

"select * from Location where zipcode in (".Location.GetNearByZipCodes(28078,5).");"

Also make sure this function returns a string with zipcodes saperated by commas ,.

You can also call Location.GetNearByZipCodes() before the query and store the returned value in a variable and then use the variable in the query the way you're used to do it.

Upvotes: 1

Related Questions