STORED PROCEDURE

Asked

Viewed 271 times

1

I have 2 procedures and I wanted to call the trial sp_is_temporada in the past sp_calcula_curso, where the value of the sp_is_temporada return will be used in procecure sp_calcula_curso, and the arguments that were passed in the past sp_calcula_curso will be used in the process 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 ;
sql procedure

  • And you want to know what to call ?

  • I read something about EXEC, but I could not understand very well, how I would pass the arguments of sp_calcula_curso in the exec of sp_is_temporada, and how I would return that too..

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)

Define how many variables you need so you can pass on Sproc.

Then in the SELECT that you want to take the results to pass as parameter in the other Sproc, you do:

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

Done this you can call to Sproc by command exec 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 ?

There’s no way to do what you want in one ?

With variables this is possible.

Analyze well and return us to any questions.

Browser other questions tagged sql procedure

You are not signed in. Login or sign up in order to post.