[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Mysql Stored proc problem

Posted on 2012-09-06
2
Medium Priority
?
452 Views
Last Modified: 2012-09-07
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
0
Comment
Question by:rutledgj
2 Comments
 

Author Comment

by:rutledgj
ID: 38374069
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 38375354
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question