Home / Questions / 125570
Accepted
Jose Yeste
Asked: 2020-12-19 08:38:09 +0800 CST 2020-12-19 08:38:09 +0800 CST

Reuse stored procedure sql server

  • 772

I'm trying to reuse the result of a procedure so I don't have to call it twice, since that way the query takes too long.

      select DW.dbo.procedimiento(CONVERT(char(10), FecLlamada, 103), 
                        CONVERT(char(5), HorLlamada, 108), 
                        CONVERT(char(10), FecContestada, 103), 
                        CONVERT(char(5), HorContestada, 108)) AS TiempoRespuestaMinutos
         (CASE WHEN Segmentacion >= 1 
                  AND Segmentacion <= 1.5 
                  AND 'TiempoRespuestaMinutos' > 150 
          THEN 0                          
          ELSE 1 END) AS CumplimientoTRG

      from llamadas

Where it says 'ResponseTimeMinutes' is where I want to put what the procedure returns.

Thank you!

sql

1 Answers

  1. Best Answer

    In order to be able to refer to the result of the procedure in SELECTwithout having to execute it a second time, you can do this by running it inside a derived table (or inside a common table expression if you prefer):

    select TiempoRespuestaMinutos,
       CASE WHEN Segmentacion >= 1 
                 AND Segmentacion <= 1.5 
                 AND TiempoRespuestaMinutos > 150 
            THEN 0                          
            ELSE 1 END AS CumplimientoTRG
  from (select Segmentacion,
               DW.dbo.procedimiento(CONVERT(char(10), FecLlamada, 103), 
                        CONVERT(char(5), HorLlamada, 108), 
                        CONVERT(char(10), FecContestada, 103), 
                        CONVERT(char(5), HorContestada, 108)) AS TiempoRespuestaMinutos
          from llamadas) t

    ...or if you prefer a common table expression:

    ;with cte as (
  select Segmentacion,
         DW.dbo.procedimiento(CONVERT(char(10), FecLlamada, 103), 
                 CONVERT(char(5), HorLlamada, 108), 
                 CONVERT(char(10), FecContestada, 103), 
                 CONVERT(char(5), HorContestada, 108)) AS TiempoRespuestaMinutos
    from llamadas
)
select TiempoRespuestaMinutos,
       CASE WHEN Segmentacion >= 1 
                 AND Segmentacion <= 1.5 
                 AND TiempoRespuestaMinutos > 150 
            THEN 0                          
            ELSE 1 END AS CumplimientoTRG
      from cte

    But even though this saves you one call per table record returned llamadas, that's still multiple calls in total. If it really costs that much to run the procedure, the query will probably still take a long time.

    Ideally, you'd want to evaluate what the procedure does and see if there isn't a better way to combine it with the main query.

    • 1