There is a situation when it is necessary to make a query to the table on several parameters. For example:

We have a table:

CREATE TABLE Table1( Id BIGINT IDENTITY, Col1 BIGINT NULL, Col2 BIGINT NULL ); INSERT INTO Table1 (Col1, Col2) VALUES (0, 0), (1, 1), (1, NULL);

And a procedure that returns a string from the table by parameters:

 CREATE PROCEDURE GetData @Prm1 BIGINT, @Prm2 BIGINT AS BEGIN SELECT * FROM Table1 t WHERE t.Col1 = @Prm1 AND t.Col2 = @Prm2 END GO

There is no problem, as long as we are not trying to pull out a string that has a column with a value of NULL. But if we decide to do this, then this procedure will not return anything to us, because NULL <> NULL , and, accordingly, we will have to rewrite the request, for example:

 CREATE PROCEDURE GetData @Prm1 BIGINT, @Prm2 BIGINT AS BEGIN SELECT * FROM Table1 t WHERE t.Col1 = @Prm1 AND t.Col2 = @Prm2 OR t.Col1 IS NULL AND @Prm1 IS NULL AND t.Col2 = @Prm2 OR t.Col2 IS NULL AND @Prm2 IS NULL AND t.Col1 = @Prm1 OR t.Col1 IS NULL AND @Prm1 IS NULL AND t.Col2 IS NULL AND @Prm2 IS NULL END GO

Is it possible to somehow get around this jumble of comparing data with NULL in a query, so that the query will still work? After all, if there are more than 2 columns in the table, and, accordingly, the parameters in the GetData procedure, then the condition in the query grows greatly, which can lead to errors

  • At a minimum, the latter condition can be reduced to COALESCE(t.Col1, @Prm1, t.Col2, @Prm2) IS NULL . - Yaant
  • one
    COALESCE(Col1, -1) = COLASCE(@Prm1, -1) AND COALESCE(Col2, -1) = COLASCE(@Prm2, -1) Instead of -1, some value of which cannot be in the data and in the query. I hope this is. Although there you can write any text value. there will be a conversion of numbers to a string, but there will be no exact value in the data - Mike
  • 2
    Alas, Null-safe compare in SQL Server is not implemented. And any whitening of WHERE ((field = @param) OR (field IS NULL AND @param IS NULL)) using functions with a guarantee will have a negative effect on performance. - Akina

0