CODEWITHSUNDEEP
mysqlfunctionstored-functions
David Sélem Garcia
David Sélem Garcia

Reputation: 89

How to make use of variable on a function

I need to make a stored function:

This is my code 

SELECT count(Dominio) FROM Thogar WHERE DOMINIO='%'

I need to make a stored function where I will write a letter between (U,C,R) and the function will replace the % in the previous code with the selected letter.

How can I do it? Thanks!

Got it working

CREATE FUNCTION `Buscar`(`param` CHAR(1))
    RETURNS INT
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE res INT;
    SELECT count(Dominio) INTO res FROM Thogar WHERE DOMINIO=param;
    RETURN res;
END

    Call buscar('C')

Upvotes: 1

Views: 42

Answers (2)

amaksr
amaksr

Reputation: 7745

This should work:

DROP FUNCTION IF EXISTS myFunc;
DELIMITER $$
CREATE FUNCTION myFunc(
    param CHAR(1)
) RETURNS INT;
BEGIN
    DECLARE res INT;
    SELECT count(Dominio) INTO res FROM Thogar WHERE DOMINIO=param;
    RETURN res;
END;
$$
DELIMITER ;

Upvotes: 1

num8er
num8er

Reputation: 19382

If You want to make stored function with only one sql query, I don't see any normal reason for it.

It will not give You performance gain.

How about simplification?

You can create view:

CREATE VIEW v_dominio_counters AS 
SELECT Dominio, count(Dominio) AS counter FROM Thogar GROUP BY Dominio

And then use it:

SELECT counter FROM v_dominio_counters WHERE Dominio = 'U' LIMIT 1;

It will always keep for You ready to use counters that is handy when You have huge table.

Upvotes: 0

Related Questions