Reuse stored procedure sql server

Question

Reuse stored procedure sql server

Navigation

#1 by (1 votes)

1

I'm trying to reuse the result of a procedure so I do not call it twice again, because it takes too long to do so.

      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 'MinutesResponseTime' is where I want to put what the procedure returns.

Thank you!

sql sql-server sql-server-2014

asked by Jose Yeste 18.12.2017 в 17:38

source

1 answer

Comment line in Laravel 5.1 Angular Rectangular Form 5: ERROR TypeError: Can not read property 'valid'

score 1 · Accepted Answer

To be able to refer to the result of the procedure in SELECT without having to execute it a second time, you can do this by executing it within a derived table (or within 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 if this saves you a call per record returned from table llamadas , there are still several calls in total. If the execution of the procedure really costs so much, the query will probably take a long time.

Ideally, evaluate what the procedure does and see if there is not a better way to combine it with the main query.