[SOLVED] Need help creating simple SPROC

[EvilCoatHanger]

mySQL Version: 5.0.18

Running on Mac OSX 10.4

Running also phpMyAdmin 2.7.0-pl2

 

I've been trying to create a simple stored procedure.. For example:

 

CREATE PROCEDURE usp_GetPaymentLocationsByZip(INOUT zipcode VARCHAR(5))
BEGIN
SELECT * FROM dpl_PaymentLocations WHERE Zip = zipcode; 
END;

 

I'm new to mySQL, but have a basic understanding of SQL. Please help, I'm going insane trying to figure this out!!

 

I get the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM dpl_PaymentLocations WHERE Zip = zipcode' at line 3

 

Everything just seems so cryptic to me.

 

Thanks in advance,

Cavin

[EvilCoatHanger]

Did I not give the appropriate information?

[fenway]

Why INOUT? It should probably be just IN.

[aschk]

You have specified your delimiter twice (

You need to change the delimiter at the start then change it back at the end.

 

CREATE PROCEDURE usp_GetPaymentLocationsByZip(IN zipcode VARCHAR(5))
DELIMITER $$
BEGIN
SELECT * FROM dpl_PaymentLocations WHERE Zip = zipcode; 
END $$
DELIMITER ;

[EvilCoatHanger]

aschk,

 

I tried what you said, and I still get the same error.

 

I'm running this through phpAdmin, through the 'SQL' queries tab. Could it be something with the way the phpAdmin & mySQL are setup?

[fenway]

Not sure.. though in this case you don't really need an BEGIN/END block at all.

[EvilCoatHanger]

Do Stored Procedures work the same way in mySQL vs SQL2000?

 

For reference, heres my current SPROC:

CREATE PROCEDURE usp_GetPaymentLocationsByZip(IN zipcode VARCHAR(5))
SELECT * FROM dpl_PaymentLocations WHERE Zip = zipcode; 

(Thank you everybody that got me this far..)

 

Example.. I could say:

 

usp_GetPaymentLocationsByZip @ZipCode=43040

 

and get a record back with all of the information pertaining to that.

 

In mySQL, (This is from my understand, please correct me if I'm wrong..), it would be...

 

CALL usp_GetPaymentLocationsByZip(43040);

 

.. but it wouldn't select anything back. Do I have to create another parameter using OUT and call that specifically?

 

 

On Another Note.. can you only pull back one record with a stored procedure?

[effigy]

Do the delimiter first:

 

DELIMITER $$
CREATE PROCEDURE usp_GetPaymentLocationsByZip(IN zipcode VARCHAR(5))
BEGIN
SELECT * FROM dpl_PaymentLocations WHERE Zip = zipcode; 
END $$
DELIMITER ;

[aschk]

AH hah, whoops, that's where i went wrong. Dang it i should have checked the mysql docs...

[EvilCoatHanger]

After further reading and spending tons of time trying to develop this stored procedure. It finally comes down to this:

SQL query:

call usp_GetPaymentLocationsByZipCode( 43040, @AgentName , @Address )

MySQL said: 

#1172 - Result consisted of more than one row 

 

From what I have read, I will need to include a CURSOR inside my Stored Procedure. Correct? I'm just trying to figure this out so I don't keep going down the wrong path.

[EvilCoatHanger]

Okay, after reading over my past posts.. I'm wondering what in the hell I was thinking.

 

I need a way to pull back multiple records by passing in a variable. I am doing a straight SELECT:

SELECT * FROM dpl_PaymentLocations WHERE Zip LIKE '45324' AND DeleteFlag = 0 ORDER BY AgentName

Which Returns:
CRICKET - C WIRELESS	85 E DAYTON YELLOWSPRINGS RD FAIRBORN, OH 45324	937-754-7350	Click Here
DISCOUNT DRUG MART #73	7617 DAYTON SPRINGFIELD FAIRBORN, OH 45324	330-725-2343	Click Here

 

I want to avoid using a straight SELECT due to SQL Injection. In SQL2000, a Stored Procedure would of been my best bet. In mySQL I don't know what the best way to do this is. Can somebody point me in the right direction?

[effigy]

I want to avoid using a straight SELECT due to SQL Injection.

 

Use mysql_real_escape_string. You don't need a procedure for this.

[EvilCoatHanger]

Problem Solved, Worked Perfectly.. Thank you effigy.

 

(Can users mark the thread as Solved?)