CODEWITHSUNDEEP
mysqlsql
user3175226
user3175226

Reputation: 3659

simple query not works if inside a mysql function

i'm using a query inside a function to get value in database, but it is returning NULL. the same query outside the function works fine.

my function

BEGIN
    RETURN (SELECT `value` FROM data WHERE `k` = 29 AND `entity` = "abr9f9" LIMIT 1);
END

the test using function and the same raw query

SELECT 
    GETSTATIC() AS static,
    (SELECT `value` FROM data WHERE `k` = 29 AND `entity` = "abr9f9" LIMIT 1) AS raw

is returning

static     raw
------     ---
NULL       175

I think this is because i'm not choosing the return field VALUE in the function.
How can I do this?

Upvotes: 0

Views: 64

Answers (1)

Kitet
Kitet

Reputation: 883

The first example of code, between BEGIN and END, isn't going to return your expected result, because this notation is used to define a procedure, while the second example is plain SQL DML.

If you want, you can read about CREATE PROCEDURE syntax, specifically about OUT parameter.

EDIT:

Try this:

create function GETSTATIC()
returns integer 
return (SELECT `value` FROM data WHERE `k` = 29 AND `entity` = "abr9f9" LIMIT 1);

then you would call it like:

select GETSTATIC() as static;

Upvotes: 2

Related Questions