How to improve this sql code?

Navigation
0

I would like to improve this code so I DO NOT REPEAT THE SAME CODE IN THE TWO IFS. I am not in the process of using procedures because I see no need. 

DECLARE @Ordem VARCHAR (7), @Valor VARCHAR(10)
SET @Ordem = 'E'
IF @Ordem = 'E'

BEGIN
  SELECT 
  P.ParcDocFinDupNum,P.ParcDocFinSeq,P.ParcDocFinDesmPag,P.ParcDocFinDataEmissao,P.ParcDocFinDataVenc,
  P.BcoNum,P.TipoCobCod,P.ParcDocFinValOrig,P.ParcDocFinValor, P.DocFinChv,E.EntNome,
  D.EmpCod,D.DocFinTipoLanc,D.DocFinProjecao, D.DocFinChv, D.EntCod, D.DocFinEspec, GetDate() as DataHoje
  FROM DOC_FIN D 
INNER JOIN PARC_DOC_FIN P ON D.EntCod = P.EntCod 
INNER JOIN ENTIDADE E ON E.EntCod = P.EntCod
WHERE  
D.DocFinTipoLanc = 'PAG' 
AND ('Todos' = 'Não' OR D.DocFinProjecao = 'Não')
ORDER BY E.EntNome ASC, RAND()

END

IF @Ordem = 'V'
SELECT 
  P.ParcDocFinDupNum,P.ParcDocFinSeq,P.ParcDocFinDesmPag,P.ParcDocFinDataEmissao,P.ParcDocFinDataVenc,
  P.BcoNum,P.TipoCobCod,P.ParcDocFinValOrig,P.ParcDocFinValor, P.DocFinChv,E.EntNome,
  D.EmpCod,D.DocFinTipoLanc,D.DocFinProjecao, D.DocFinChv, D.EntCod, D.DocFinEspec, GetDate() as DataHoje
  FROM DOC_FIN D 
INNER JOIN PARC_DOC_FIN P ON D.EntCod = P.EntCod 
INNER JOIN ENTIDADE E ON E.EntCod = P.EntCod
WHERE  
D.DocFinTipoLanc = 'PAG' 
AND ('Todos' = 'Não' OR D.DocFinProjecao = 'Não')
ORDER BY convert(varchar,P.ParcDocFinDataVenc,103), E.EntNome ASC

END
    
asked by anonymous 30.05.2018 / 22:36

1 answer

2

You can use a CASE WHEN expression [Boolean expression] THEN [expression] END in the ORDER BY clause, to remove the IFs ... ELSEs:

link

EDIT: In your case, it would look like this: 

...
ORDER BY
CASE WHEN @Ordem = 'E' THEN E.EntNome
     WHEN @Ordem = 'V' THEN CONVERT(VARCHAR, P.ParcDocFinDataVenc, 103)
END ASC,
CASE WHEN @Ordem = 'E' THEN RAND()
     WHEN @Ordem = 'V' THEN E.EntNome
END ASC
    
30.05.2018 / 22:47
Post php only with all fields filled out Nothing happens when calling .php file via Ajax call in pure Javascript