varchar parameter in Store Procedure

Question

varchar parameter in Store Procedure

Navigation

#1 by (6 votes)
#2 by (2 votes)

3

I have a store procedure in mysql 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 me the name of the field that I pass in the result. That is, it's like I've done the select like so:

SELECT id as id, 'nome_campo' as value

But what I wanted was:

SELECT id as id, nome_campo_que_eu_passei as value

How to make this return be correct?

mysql stored-procedures

asked by anonymous 22.10.2014 / 14:24

2 answers

2

Parameters in stored procedures can be of two distinct input types ( IN ) or OUT

The parameter type should be informed when creating the procedure:

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 is no longer declared as the name of a particular table column.

My tip about nomenclature, so that there are no problems, is to use a standard nomenclature of parameters such as 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 field of all table tuples that have the field value equal to the p_campo

parameter.

I hope to have helped, any questions are available!

22.10.2014 / 17:55

XML file without associated style information Find sum of values in array

score 6 · Accepted Answer

You will need to mount a SQL dynamically, using campo as 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 $$