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?
 
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
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.