1

Basically I am trying to refer to my table in mysql function, so that in my query I can say "from x" as in x is a parameter of the function, so that someone can put in the table they want the function to run on.

    CREATE DEFINER=`root`@`localhost` FUNCTION `somefunction`(t varchar(8), num integer) RETURNS int(8)
    BEGIN
    DECLARE result integer(12);
    DECLARE test varchar(12);
    SET result = 0;
    SET test = t;
    select integer * 5 INTO result from x;
    return result;
    END

Basically when I do somefunction(thisisthetableiwant, 5) I get an error saying that it cannot find 'test in field list' so it isn't setting the table to what I put in the parameter, currently I have the part "from x" hardcoded with the table I want and it works but I need to make it so I can have a parameter incase I need to use the function on another table

Improve this question
4
  • can't do it like this. You cannot use a parameter/variable/contents-of-field as an sql keyword. You need to build your query as a string, insert your table name into that, then execute the string. Commented Jul 14, 2014 at 19:02
  • e.g. set foo='users'; select * from @foo will not work. Commented Jul 14, 2014 at 19:02
  • So I've looked at other answers like stackoverflow.com/questions/8549619/… and it seems that I cannot do that because I am creating a function not a procedure because I have tried creating a concat string and I get the "error 1336: dynamic sql is not allowed in stored function or trigger sql statement" Commented Jul 14, 2014 at 19:15
  • But your function can call a stored procedure! Yeah, that's ugly. Commented Jul 14, 2014 at 20:58
Related questions
1
Creating mysql Function with parameters
3
Variable Table Name MySQL Stored Function
0
MySQL stored function with table
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.