udf return column name not value

--000e0cd4850886e9220481d23c98
Content-Type: text/plain; charset=ISO-8859-1

CREATE FUNCTION getcolumnvalue(id int,columnname varchar(30))
RETURNS varchar(50) DETERMINISTIC
READS SQL DATA
begin
declare retval varchar(50);
select columnname into retval from user where ID = id ;
return retval;
end;

I want get value of related column but it return column name.
ex:- ('tom' what I want but it return 'name')
plz tell what the wrong of this

thank you

--000e0cd4850886e9220481d23c98--

RE: udf return column name not value

You'll have to do something like this:

SET @sql :=3D CONCAT('select ',columnname,' into retval from user where ID=
=3D',id);
PREPARE mySt FROM @sql;
EXECUTE mySt;



-----Original Message-----
From: chamila gayan [mailto:cgchamila@gmail.com]
Sent: Monday, March 15, 2010 12:58 AM
To: mysql@lists.mysql.com
Subject: udf return column name not value

CREATE FUNCTION getcolumnvalue(id int,columnname varchar(30))
RETURNS varchar(50) DETERMINISTIC
READS SQL DATA
begin
declare retval varchar(50);
return retval;
end;

I want get value of related column but it return column name.
ex:- ('tom' what I want but it return 'name')
plz tell what the wrong of this

thank you

This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: udf return column name not value

--005045029d084651690481e3aecd
Content-Type: text/plain; charset=ISO-8859-1

Thank you for your speedy reply.

I tried on your way. But it gives *error* as "*Dynamic SQL is not allowed in
stored function or trigger*". I'm using *MySql 5* as my sever. How can I
solve that.

thank you

On Mon, Mar 15, 2010 at 11:43 PM, Gavin Towey wrote:

> You'll have to do something like this:
>
> SET @sql := CONCAT('select ',columnname,' into retval from user where
> ID=',id);
> PREPARE mySt FROM @sql;
> EXECUTE mySt;
>
>
>
> -----Original Message-----
> From: chamila gayan [mailto:cgchamila@gmail.com]
> Sent: Monday, March 15, 2010 12:58 AM
> To: mysql@lists.mysql.com
> Subject: udf return column name not value
>
> CREATE FUNCTION getcolumnvalue(id int,columnname varchar(30))
> RETURNS varchar(50) DETERMINISTIC
> READS SQL DATA
> begin
> declare retval varchar(50);
> return retval;
> end;
>
> I want get value of related column but it return column name.
> ex:- ('tom' what I want but it return 'name')
> plz tell what the wrong of this
>
> thank you
>
> This message contains confidential information and is intended only for the
> individual named. If you are not the named addressee, you are notified that
> reviewing, disseminating, disclosing, copying or distributing this e-mail is
> strictly prohibited. Please notify the sender immediately by e-mail if you
> have received this e-mail by mistake and delete this e-mail from your
> system. E-mail transmission cannot be guaranteed to be secure or error-free
> as information could be intercepted, corrupted, lost, destroyed, arrive late
> or incomplete, or contain viruses. The sender therefore does not accept
> liability for any loss or damage caused by viruses or errors or omissions in
> the contents of this message, which arise as a result of e-mail
> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
> 94089, USA, FriendFinder.com
>

--005045029d084651690481e3aecd--