Parameters for query in sql server

Asked

Viewed 266 times

1

Good morning..

I have a query in sql server with date and status columns, I need to bring the result by the date typed or by the state typed or by the two, as I can do this right in the query in sql server. Ex if the user type only the start and end date bring the result, and if the user also type the state also bring the result by the date typed 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
sql-server

  • You can do it for a stored procedure

  • 1

    @Juniorguerreiro: (1) If you have GROUP BY you don’t need DISTINCT. This only slows down the query... (2) The filter SD2.D2_TES IN can be optimized through a simple technique. See Article https://portosql.wordpress.com/2018/10/04/construindo-codigos-t-sql-eficientes-sargability/

1 answer

1


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

"parameter @start date is null or D2_EMISSAO = @start date"

Sure, 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 (you did not pass the parameters) or compare.
Either the state is null (not passed) or compares.

  • Thank you very much it worked out here vlw

Browser other questions tagged sql-server

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