Solved

Can't get my function to work on MySql

Posted on 2011-02-17
5
269 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…

831 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