Parameter varchar in Store Procedure

Question

I have a mysql store as follows:

CREATE PROCEDURE nome_procedure(campo VARCHAR(15))
BEGIN
    SELECT id as id, campo as value
    FROM tabela
    etc etc etc...;
END $$

However, since the parameter I am passing is of type VARCHAR, it returns the name of the field I pass in the result. I mean, it’s like I’ve done select like this:

SELECT id as id, 'nome_campo' as value

But what I’d like is:

SELECT id as id, nome_campo_que_eu_passei as value

How to make this return be correct?

Answer 1

You will need to mount an SQL dynamically, using campo as a variable:

CREATE PROCEDURE nome_procedure(campo VARCHAR(15))
BEGIN
    SET @temp1 = CONCAT('SELECT id as id, ', campo, ' as value FROM tabela etc etc etc');

    PREPARE stmt1 FROM @temp1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END $$

Answer 2

Parameters in stored procedures can be of two different input types (IN) or leaving (OUT).

The type of the parameter must be informed when creating the parameter:

CREATE PROCEDURE nome_procedure(IN campo VARCHAR(15))
BEGIN
    SELECT id as id, campo as value
    FROM tabela
    etc etc etc...;
END $$

Also check that the name of your parameter has not already been declared as the name of a column of a given table.

My tip about naming, so that no problems occur, is to use a standard parameter nomenclature, for example p_campo or p_coluna.

CREATE PROCEDURE nome_procedure(IN p_campo VARCHAR(15))
BEGIN
    SELECT id as id, campo as value
    FROM tabela
    WHERE campo = p_campo;
END

This code returns the id and the field of all table tuples that have the field value equal to the p_field parameter.

I hope I’ve helped, any doubt I’m available!