Assign a IS NOT NULL condition to a Variable?

Question

Assign a IS NOT NULL condition to a Variable?

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

Friends I have a query as follows

WHERE 
     bt.ProductoID= CASE WHEN @ProductoID IS NOT NULL THEN @ProductoID ELSE IS NOT NULL END
  AND (bt.talla= @talla AND bt.Año =@Año)

The problem that I have is that I have to do a search procedure, the user can send one or up to 5 parameters to filter the search, my idea is that when a filter is null, it will not be taken into account, as They will give account I am using in "CASE" as a ternary operator but the problem I have is that the "IS NOT NULL" I can not assign, if someone could help me or tell me how I can do the search.

sql sql-server

asked by Aetos2501 27.06.2018 в 03:19

source

2 answers

Error in Python: NameError name XXX is not defined Search for columns with style display none and remove them

score 2 · Answer 1

If you are looking to not use the filter when @ProductoID is NULL (or any other of the parameters) you can do the following:

WHERE ( @ProductoID IS NULL OR bt.ProductoID = @ProductoID )
      AND (@Año IS NULL OR bt.Año = @Año)
      -- y así con cada uno de los filtros

This form is very useful to put together a very flexible query, but it has an eventual problem, which is the product of something called Parameter sniffing , in a summarized way: the engine will optimize an SP considering the cardinality of the parameters with which it is invoked, and as there is only one optimization for the SP, it might happen that it works well for a particular combination of filters but for another quite the opposite. This is very common to see when an SP, invoked with a set of parameters, retrieves a large number of records and with another combination only one, in one of the two forms you will surely have a penalty in the performance.

score 1 · Answer 2

In this case to my way of thinking, you can do the following:

WHERE bt.ProductoID = ISNULL(@ProductoID,bt.ProductoID)
AND bt.Talla=ISNULL(@Talla,bt.Talla)
AND bt.Año=ISNULL(@Año,bt.Año)

How does it work? What ISNULL does is that if the Search parameter is not null, it restricts the search to what matches that parameter, but if that parameter arrives as null then it takes everything that exists in bt.Year.

Now there is an exception with the handling of null values, in Sql server you can not compare if Null = Null then you must use a double ISNULL assigning a value if it is null, and the same on the right side, for example a 0 for make a 0 = 0 leaving as follows.

 WHERE ISNULL(bt.ProductoID,0) = ISNULL(@ProductoID,ISNULL(bt.ProductoID,0))
 AND ISNULL(bt.Talla,0)=ISNULL(@Talla,ISNULL(bt.Talla,0))
 AND ISNULL(bt.Año,0)=ISNULL(@Año,ISNULL(bt.Año,0))

Added to this, I recommend that if you are working with parameters and variables, identify with @P the parameters and @V the variables, this by good practices.

@PParametro
@VVariable

And a small practical example:

DECLARE @PDescripcion VARCHAR(30)=NULL, 
        @PId INT

DECLARE @VDescripcion AS VARCHAR(30)='Descripcion1'

DECLARE @Tabla AS TABLE(
    ID INT,
    Descripcion VARCHAR(30)
)

INSERT INTO @Tabla
SELECT 1, 'Descripcion1'

INSERT INTO @Tabla
SELECT 2, 'Descripcion2'

INSERT INTO @Tabla
SELECT NULL, 'Descripcion3'


--Utilizando el parametro
SELECT *
FROM @Tabla
WHERE Descripcion = ISNULL(@PDescripcion,Descripcion)   --Si el parametro descripcion es nulo entonces toma todo el contenido en Descripcion

--Utilizando la variable
SELECT *
FROM @Tabla
WHERE Descripcion = ISNULL(@VDescripcion,Descripcion) --Como la variable no es nula busca las coincidencias con la variable

--Comparando valores nulos
SELECT *
FROM @Tabla
WHERE ID = ISNULL(@PDescripcion,ID) --No podras ver el valor nulo por no poder comparar null con null

SELECT *
FROM @Tabla
WHERE ISNULL(ID,0) = ISNULL(@PDescripcion,ISNULL(ID,0))-- Puedes ver el registro null ya que comparas en lugar de null = null con 0 = 0

I hope my answer is helpful.