Can't get my function to work on MySql

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

oneaAsked:
Who is Participating?
 
stezebConnect With a Mentor Commented:
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
 
SharathData EngineerCommented:
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
 
oneaAuthor Commented:
Same problem:

Error at line 5, error in sql syntax.
Undeclared variable sReturnString.
Syntax error on the return line;
0
 
SharathData EngineerCommented:
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
 
oneaAuthor Commented:
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
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.