Parameters for query in query in sql server

Navigation
1

Good morning ..

I have a query in sql server with date and state columns, I need to bring the result by the typed date or by the typed state or both, how can I do this right in the query in sql server. Ex if the user types only the start and end date bring the result, and if the user also enter the status also bring the result by the date entered and also by the selected state.

follow my query 

SELECT DISTINCT 
CONCAT(SA1.A1_COD, ' - ', SA1.A1_NOME) AS CLIENTE,
SA3.A3_NOME                            AS VENDEDOR, 
SD2.D2_EST                             AS ESTADO,
SUM(CAST(SD2.D2_QUANT AS DECIMAL(18,2)))                      AS QTDA, 
SUM(SD2.D2_TOTAL)                      AS [TOTAL SEM IPI], 
SUM(SD2.D2_VALBRUT)                    AS [TOTAL COM IPI]
FROM SD2010 AS SD2 
INNER JOIN SA1010 AS SA1 WITH(NOLOCK) ON SA1.A1_COD = SD2.D2_CLIENTE 
INNER JOIN SB1010 AS SB1 WITH(NOLOCK) ON SB1.B1_COD = SD2.D2_COD
INNER JOIN SC5010 AS SC5 WITH(NOLOCK) ON SC5.C5_NUM = SD2.D2_PEDIDO
INNER JOIN SA3010 AS SA3 WITH(NOLOCK) ON SA3.A3_COD = SC5.C5_VEND1
WHERE SD2.D2_TES IN ('501', '502', '505', '506', '507', '509', '513', '514', 
'518', '591', '592', '594', '596', '597', '603', '604', '606', '607')
AND SD2.D2_EMISSAO BETWEEN CONVERT(datetime, '08/01/2018' , 103) AND 
CONVERT(datetime, '08/01/2018' , 103) 
AND SA1.A1_FILIAL IN ('01', '') 
AND SD2.D2_FILIAL IN ('01', '') 
AND SB1.B1_FILIAL IN ('01', '')
AND SD2.D_E_L_E_T_ <> '*' 
AND SA1.D_E_L_E_T_ <> '*' 
AND SB1.D_E_L_E_T_ <> '*' 
GROUP BY SA1.A1_COD, SA1.A1_NOME, SA3.A3_NOME, SD2.D2_EST
ORDER BY [TOTAL COM IPI] DESC
    
asked by anonymous 10.10.2018 / 02:23

1 answer

1

You can do the comparison if the parameter is null and use OR operator if it is not, if it were "say" the logic would look like this:

  

"@datainicial parameter is null or D2_EMISSAO = @datainicial"

Of course, your case using BETWEEN , and the same for the state: 

WHERE .... 
AND 
( 
  ( 
    @DATAINI IS NULL 
    AND 
    @DATAFIM IS NULL 
  ) 
  OR 
  ( 
    sd2.d2_emissao BETWEEN 
    CONVERT(datetime, @DATAINI , 103) 
    AND 
    CONVERT(datetime, @DATAFIM , 103) 
  ) 
) 
) 
AND 
( 
  @ESTADO IS NULL 
  OR 
  campo_estado = @ESTADO 
)

That is, either the dates are null (has not passed the parameters) or compares. Either the state is null (has not passed) or compares.

    
10.10.2018 / 02:50
Error # 1064 in CREATE PROCEDURE - PhpMyAdmin SQL Align lists of different sizes in the div in Bootstrap