Pass table field as parameter in a procedure

Question

Pass table field as parameter in a procedure

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I made this procedure to bring records of a table between dates, but I need to pass the date field of the table to the command between function, follow the code:

DELIMITER //
CREATE PROCEDURE pro_get_gastos(nome_tabela VARCHAR(20), data_ini DATETIME, data_fin DATETIME)
BEGIN
SET @tabela = CONCAT('SELECT * FROM ',nome_tabela,' WHERE ', AQUI TERIA QUE PASSAR O CAMPO DATA, 'BETWEEN', data_ini, ' AND ', data_fin,'''')
PREPARE consulta_gastos FROM @tabela;
EXECUTE consulta_gastos;
END //

How can I resolve this?

mysql procedure stored-procedures

asked by anonymous 18.06.2015 / 18:07

2 answers

C # decimal field in Form When programming an ARM microcontroller is it possible to change its clock after running SystemInit ()?

score 1 · Answer 1

Do this:

CREATE PROCEDURE pro_get_gastos @nome_tabela sysname,
                                @data_mov    sysname,
                                @data_ini    datetime, 
                                @data_fim    datetime AS
BEGIN 
    DECLARE @sql nvarchar(4000)
    SELECT @sql = ' SELECT * ' +
              ' FROM ' + QUOTENAME(@nome_tabela) +
              ' WHERE ' + QUOTENAME(@data_mov) + ' BETWEEN ' + 
              ' @data_ini AND @data_fim ' 

   EXEC sp_executesql @sql, N'@data_ini datetime, @data_fim datetime', @data_ini, @data_fim
END

Edit: In MySQL you can do this:

delimiter // 
CREATE PROCEDURE pro_get_gastos (IN nome_tabela CHAR(20),
                                 IN data_mov    CHAR(20),
                                 IN data_ini    datetime, 
                                 IN data_fim    datetime)
BEGIN
    SET @sql = CONCAT('SELECT * FROM ', nome_tabela, ' WHERE ', data_mov, ' BETWEEN ', data_ini, ' AND ', data_fim);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
//
delimiter ;

score 0 · Answer 2

Create a parameter beyond the nome_tabela , where:

CREATE PROCEDURE pro_get_gastos(
    nome_tabela VARCHAR(20), 
    nome_data VARCHAR(20), 
    data_ini DATETIME, 
    data_fin DATETIME)

And in the concatenation you pass the same passed the table

CONCAT('SELECT * FROM ',nome_tabela,' WHERE ', nome_data, ...