asked on

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

rutledgj

ASKER

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.

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial