question about stored Procedure

[ballouta]

Hello

 

I am writing a Stored Procedure that take zip code as IN parameter and search for the ZIP CODE in zipcodes table

if exists it should provide city and state as outputs

otherwise, set the default and state as San Francisco and CA.

 

The question is HOW do i check if the zip code was found?

 

delimiter //
CREATE PROCEDURE zip_code_search (IN zip_in CHAR, OUT city_out VARCHAR, OUT state_out CHAR)
BEGIN
SELECT * FROM zipcodes where zip_code = zip_in;

END
delimiter

thanks

[mikosiko]

Basically you have 2 options:

a) Do the checking in the same SP; for that you have to modify your SELECT and use it with the SELECT... INTO syntax like:

        SELECT city, state INTO city_out, state_out FROM zipcodes WHERE zip_code = zip_in;
        // and validate the values in city_out or state_out to determine if they have value after the select

 

b) or do the checking in your PHP code after you call the SP using any of the mysql API functions (mysql_num_rows($resultset), mysql_fetch.. etc).

 

As an additional suggestion: you should include in you SP the necessary coding to control possibles errors using the DECLARE HANDLER syntax.

 

a good source to learn how to use Stored Procedure is this PDF document... Error Handling is covered starting at the end of page 29

[The Little Guy]

You can do something like this:

 

delimiter //
CREATE PROCEDURE zip_code_search (IN zip_in CHAR, OUT city_out VARCHAR, OUT state_out CHAR)
BEGIN
SET @was_found  = (SELECT 1 FROM zipcodes where zip_code = zip_in);
IF @was_found = 1 THEN
	-- The value was found
ELSE
	-- The value wasn't found
END IF;
END
delimiter

 

This line:

SET @was_found  = (SELECT 1 FROM zipcodes where zip_code = zip_in);

@was_found will either equal 1 (found) or NULL (not found)

[mikosiko]

You can do something like this:

 

delimiter //
CREATE PROCEDURE zip_code_search (IN zip_in CHAR, OUT city_out VARCHAR, OUT state_out CHAR)
BEGIN
SET @was_found  = (SELECT 1 FROM zipcodes where zip_code = zip_in);
IF @was_found = 1 THEN
	-- The value was found
ELSE
	-- The value wasn't found
END IF;
END
delimiter

 

This line:

SET @was_found  = (SELECT 1 FROM zipcodes where zip_code = zip_in);

@was_found will either equal 1 (found) or NULL (not found)

 

and how do you get the values for city_out and state_out that the OP needs?... doing other SELECT?.... that is the function of SELECT INTO 

[The Little Guy]

He can do this then:

 

delimiter //
CREATE PROCEDURE zip_code_search (IN zip_in CHAR, OUT city_out VARCHAR, OUT state_out CHAR)
BEGIN
create temporary table if not exists city(city char(15), state char(2));
insert into city select city, state from zipcodes where zip_code = zip_in;
set @rows = (select count(*) from city);

IF @rows = 1 THEN
	-- The value was found
	select * from city;
ELSE
	-- The value wasn't found
END IF;
END//

[mikosiko]

again... that is the function of SELECT INTO...

 

your code doesn't make any sense at all... now you included a temporary table and one insert which IS NOt what the OP is asking for... go ahead and read again his request.

[The Little Guy]

your code doesn't make any sense at all...

 

Or do you just not understand it?

 

In my eyes it does exactly what he wants, I just didn't set the default to San Francisco, CA

 

mine takes the city (if found) and and state and adds it to a temp table.

 

it counts the rows in the temp table

 

if there is 1 row it was found, if there wasn't a row nothing was found.

[mikosiko]

your code doesn't make any sense at all...

 

Or do you just not understand it?

 

In my eyes it does exactly what he wants, I just didn't set the default to San Francisco, CA

 

mine takes the city (if found) and and state and adds it to a temp table.

 

it counts the rows in the temp table

 

if there is 1 row it was found, if there wasn't a row nothing was found.

 

Oh boy.... Oh boy...

 

Your "code"

delimiter //
CREATE PROCEDURE zip_code_search (IN zip_in CHAR, OUT city_out VARCHAR, OUT state_out CHAR)
BEGIN
create temporary table if not exists city(city char(15), state char(2));
insert into city select city, state from zipcodes where zip_code = zip_in;
set @rows = (select count(*) from city);

IF @rows = 1 THEN
	-- The value was found
	select * from city;
ELSE
	-- The value wasn't found
END IF;
END//

 

how it should be (in its simple form no adding error handlers)

delimiter //
CREATE PROCEDURE zip_code_search (IN zip_in CHAR, OUT city_out VARCHAR, OUT state_out CHAR)
BEGIN
        SET city_out = '';
        SELECT city, state INTO city_out, state_out FROM zipcodes WHERE zip_code = zip_in;
IF (city_out = '') THEN
                SET city_out = 'San Francisco'; 
                SET state_out = 'CA';
END IF;
END//

 

and yes... you are right... I don't understand your "fantastic code" ... it is FUBAR!!  do yourself a favor a read a little the document that I did post for the OP  you really need it.

 

 

[The Little Guy]

Okay, I guess I see I looked into the OUT parameter, and I understand where you're going.

[mikosiko]

Okay, I guess I see I looked into the OUT parameter, and I understand where you're going.

 

great

[ballouta]

Thank you ALL

[ballouta]

Hello again!

 

I am wondering if this SP can be solved starting with this statement:

If exists (SELECT * FROM zipcodes where zip_code = zip_in)
Then

 

another thing i would like to know, can we user a cursor to fetch the City and State names?

 

if yes how would the SP look like?

 

I appreciate ur help

thanks

[mikosiko]

Hello again!

 

I am wondering if this SP can be solved starting with this statement:

If exists (SELECT * FROM zipcodes where zip_code = zip_in)
Then

 

To validate the existence of the zip_code ...yes... however will be more efficient to write that in this way

  IF EXISTS (SELECT 1 FROM zipcodes WHERE zip_code = zip_in) THEN
     /// whatever
  ELSE
    // whatever
  END IF

 

another thing i would like to know, can we user a cursor to fetch the City and State names?

 

if yes how would the SP look like?

No clear why you will like to do that... answer depend on your objectives... why you don't tell us what exactly are you trying to do and we will try to answer accordingly.

 

Cursors are RecordSet and you can process them almost exactly the same as you process them in PHP, therefore if your objective is return in some way the city and state to your PHP code my original example could do exactly that without any further complication.

 

But as I said... no clear which are your objectives to give you a better answer

 

[ballouta]

Thank you for ur reply

I am asking specific questions because it is a homework and I am trying to solve it using some techniques

I saw in class.

 

i studies If Exists, this is why

Moreover, I wanted to know

if I say

 

If exists (SELECT * FROM zipcodes where zip_code = zip_in)
THEN

how do i assign the arguments state and city OUT

we never had such example in class

 

thank you

[mikosiko]

great... glad to help... read the document that I posted for you... it will help you to understand better Functions/Procedures and Cursors usage

 

how do i assign the arguments state and city OUT

we never had such example in classever

 

if you use the IF then you can't assign the city_out and state_out in that select  that is why a much better solution is to use the SELECT INTO syntax that I posted before... with the IF you are able to only check for existence of the zip code... to return the city_out, state_out in that schema you must to make another select which will be totally unneficient 

[ballouta]

I understand

I appreciate your help.

I will read that document,

 

Thank you