STORED PROCEDURE

1

I have 2 procedures and I wanted to call the procedure sp_is_temporada in the procedure sp_calcula_curso , and the value that the procedure sp_is_temporada return will be used in procecure sp_calcula_curso , and the arguments that were passed in procedure sp_calcula_curso will be used in procedure sp_is_temporada .

    DROP PROCEDURE IF EXISTS SP_IS_TEMPORADA;
    DELIMITER //
    CREATE PROCEDURE SP_IS_TEMPORADA(IN SPA_CURSO_ID INT, IN SPA_DATA DATE, IN SPA_CURSO_NAVIGATION_ID INT, INOUT SPA_TEMPORADA INT)
BEGIN
    SELECT '1' INTO SPA_TEMPORADA FROM curso_temporada WHERE curso_id = SPA_CURSO_ID and SPA_DATA BETWEEN dt_inicio and dt_fim and IFNULL(curso_navigation_id,1) = IFNULL(SPA_CURSO_NAVIGATION_ID,1);
END //
    DELIMITER ;





   DROP PROCEDURE IF EXISTS SP_CALCULA_VALOR_CURSO;
   DELIMITER //
   CREATE PROCEDURE SP_CALCULA_VALOR_CURSO (IN SPA_CURSO_ID INT, IN SPA_CURS0_MOEDA_ID INT, IN SPA_IDADE INT, IN SPA_DATA DATE, INOUT SPA_VALOR_CURSO DOUBLE(20,2))
BEGIN        
    SELECT 'valor' INTO SPA_VALOR_CURSO FROM curso_preco2 WHERE curso_id = SPA_CURSO_ID AND moeda_id = SPA_CURS0_MOEDA_ID AND SPA_IDADE BETWEEN idade_de  AND idade_ate;        
END//
     DELIMITER ;
    
asked by anonymous 10.09.2015 / 15:38

1 answer

1

You have to define variables.

DEFINE @NomeVariavel_1 as INT
DEFINE @NomeVariavel_2 as VARCHAR(100)
DEFINE @NomeVariavel_3 as DATETIME
DEFINE @NomeVariavel_4 as VARCHAR(100)

Set how many variables you need to pass in Sproc .

Then in% w / o you want to get the results to pass as a parameter in another Sproc , you do:

SELECT
    @NomeVariavel_1 = Campo_1,
    @NomeVariavel_2 = Campo_2,
    @NomeVariavel_3 = Campo_3,
    @NomeVariavel_4 = Campo_4,
FROM
    Tabela

When you do this, you can call Sproc with the command SELECT , setting the variables as parameters.

EXEC SP_NOME_STORED_PROCEDURE @NomeVariavel_1, @NomeVariavel_2, @NomeVariavel_3, @NomeVariavel_4

Now

Why do you need two Sproc ?

Can not do what you want in one?

With variables this is possible.

Please review and return to us for any questions.

    
10.09.2015 / 15:58