Solved

Can't get my function to work on MySql

Posted on 2011-02-17
5
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 41

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 41

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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