Mysql Stored proc problem

I have the following in a stored proc. It will not compile. What am I doing wrong and is there a better way to do this?  FYI This is version 5.0.91. (I know it is old but they won't update it here).


DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `edidata`.`CloneLabOrg`(NewProviderName VARCHAR(25), 
				      NewLocation VARCHAR(25), 
				      NewOrg VARCHAR(50),
				      OldProviderName VARCHAR(25),
				      OldOrg VARCHAR(50))
    BEGIN
	DECLARE MyTranCode VARCHAR(10);
	DECLARE	MyDBase VARCHAR(25);
	DECLARE	MyDBTable VARCHAR(25);
	DECLARE	MyDBField VARCHAR(50);
	DECLARE	MySegment VARCHAR(5);
	DECLARE	MyElement VARCHAR(4);
	DECLARE	MyDelimitedPos VARCHAR(4);
	DECLARE	MyFieldIndex INT;
	DECLARE	MyLabOrg VARCHAR(50);
	DECLARE	MyProfile VARCHAR(20);
	DECLARE	MyLabel VARCHAR(30);
		
		SELECT MyTranCode = Trancode,
		       MyDbase = DBase,
		       MyDbTable = DBTable,
		       MyDBField = DBField,
		       MySegment = Segment,
		       MyElement = Element,
		       MyDelmitedPos = DelimitedPos,
		       MyFieldIndex = FieldIndex,
		       MyLabOrg = LabOrg,
		       MyProfile = Profile,
		       MyLabel = Label
		FROM labprofiles
		WHERE Provider = OldProviderName
		AND Org = OldOrg;
		
		INSERT INTO LabProfiles (Provider, TranCode, DBase, DBTable, DBField, Segment, Element,DelimitedPos,FieldIndex,Org,
					 LabOrg,Location,Profile,Label) VALUES
					 (NewProviderName,MyTranCode, MyDBase, MyDBTable, MyDBField, MySegment, MyElement, MyDelimitedPos, 
					 MyFieldIndex, NewOrg, MyLabOrg, NewLocation, MyProfile, MyLabel);
					 
		SELECT LAST_INSERT_ID();
    END$$

DELIMITER ;

Open in new window



Error:

Error Code: 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 '
      DECLARE      MyDBase varchar(25),
      DECLARE      MyDBTable varchar(25),
      DECLARE      MyDBFiel' at line 9
rutledgjAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rutledgjAuthor Commented:
Actually I did get this to compile but when I run it I get this:

45 row(s) returned

Execution Time : 0.028 sec
Transfer Time  : 0.001 sec
Total Time     : 0.029 sec
---------------------------------------------------

Query: call CloneLabOrg('AA999','AA33AA','DUBYA','1100AT','CCN')

Error Code: 1048
Column 'TranCode' cannot be null


I think the problem is I'm trying to create a clone of the existing provider but there are 45 rows that need to be recreated. How would I do this?  The data would be the same in these fields:

TranCode, DBase, DBTable, DBField, Segment, Element,DelimitedPos,FieldIndex, Profile,Label.

The Provider, Location, Org would get the new values passed in.

I'm basically trying to clone the existing 45 rows in the table but with the 3 new values above.
johanntagleCommented:
You don't process multiple rows in a stored procedure that way.  Either you use a cursor and loop around that cursor, or in this case since it looks like  you can do it using in a INSERT-SELECT statement, use a prepared statement.  I don't have time to give you an example right now but here are links to start you off:

1.  Using cursors:
http://www.kbedell.com/2009/03/02/a-simple-example-of-a-mysql-stored-procedure-that-uses-a-cursor/

2.  Using INSERT-SELECT and prepared statement:
http://dev.mysql.com/doc/refman/5.5/en/insert-select.html 
http://www.it-iss.com/mysql/mysql-writing-dynamic-sql-in-stored-procedures/
http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.