Solved

Can't get my function to work on MySql

Posted on 2011-02-17
5
268 Views
Last Modified: 2012-05-11
I try to create following function. When i try to execute this script, I get syntax errors.

CREATE FUNCTION GetPartnerFieldValue(partnerId BIGINT, fieldCode VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
	DECLARE sReturnString VARCHAR(255);

	SELECT * FROM (
		SELECT
			f.value
		INTO
			sReturnString
		FROM 
			PARTNERFIELD f,
			PARTNERFIELDTYPE ft
		WHERE
			f.partner_id = @partnerId
			AND f.partnerFieldType_id = ft.id
			AND ft.code = @fieldCode
	) result;

	RETURN sReturnString;
END;

Open in new window

0
Comment
Question by:onea
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34914070
Can you try this?
CREATE FUNCTION GetPartnerFieldValue(partnerId BIGINT, fieldCode VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
	DECLARE sReturnString VARCHAR(255);

		SELECT
			f.value
		INTO
			sReturnString
		FROM 
			PARTNERFIELD f,
			PARTNERFIELDTYPE ft
		WHERE
			f.partner_id = @partnerId
			AND f.partnerFieldType_id = ft.id
			AND ft.code = @fieldCode;

	RETURN sReturnString;
END;

Open in new window

0
 

Author Comment

by:onea
ID: 34914102
Same problem:

Error at line 5, error in sql syntax.
Undeclared variable sReturnString.
Syntax error on the return line;
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34914122
How about this?
CREATE FUNCTION GetPartnerFieldValue(partnerId BIGINT, fieldCode VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
	DECLARE sReturnString VARCHAR(255);

	SELECT * INTO
			sReturnString FROM
		 (
		SELECT
			f.value
		FROM 
			PARTNERFIELD f,
			PARTNERFIELDTYPE ft
		WHERE
			f.partner_id = @partnerId
			AND f.partnerFieldType_id = ft.id
			AND ft.code = @fieldCode
	) result;

	RETURN sReturnString;
END;

Open in new window

0
 

Author Comment

by:onea
ID: 34914142
Still the same.

It looks to me that the DECLARE function isn't working.
I get the first error when declaring the sReturnString variable.
And I think this causes the errors for rest of the script.
0
 
LVL 1

Accepted Solution

by:
stezeb earned 500 total points
ID: 34920416
Hi!

You need to change the delimiter, when you're creating functions.
For details on this, look here:
http://dev.mysql.com/doc/refman/5.5/en/stored-programs-defining.html

Also SELECT from line 7 is messed up and useless, so put it away (like in comment #1).

In the end, it should look like this:
DELIMITER $$
CREATE FUNCTION GetPartnerFieldValue(partnerId BIGINT, fieldCode VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
	DECLARE sReturnString VARCHAR(255);

		SELECT
			f.value
		INTO
			sReturnString
		FROM 
			PARTNERFIELD f,
			PARTNERFIELDTYPE ft
		WHERE
			f.partner_id = @partnerId
			AND f.partnerFieldType_id = ft.id
			AND ft.code = @fieldCode;

	RETURN sReturnString;
END$$
DELIMITER ;

Open in new window

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now