CODEWITHSUNDEEP
sql-servert-sql
Suzane
Suzane

Reputation: 203

Sql where clause to return everything in case filter is null

I have a sql table below -

SrNo  Name Value
1      A    X1
2      B    NULL
3      C    X3
4      D    X4
5      E    NULL
6      F    NULL

I am trying to get the all the records from the table to with below two conditions - a) In case the filter on the '@Value' column is null as below then it should return all the records including null

BEGIN
 Declare @Value varchar(50)
 SET @Value = NULL
 SELECT * from TestTable where Value = @Value
END

b) In case if the @Value is provided which is not null then it should return the column(This surely works)

I tried to implement below conditions

BEGIN
     Declare @Value varchar(50)
     SET @Value = NULL
     SELECT * from TestTable where Value = IIF(@Value is NULL,Value,@Value)
    END

But above removes the Null columns as well . Any help or suggestion would be deeply appreciated.

Upvotes: 2

Views: 1749

Answers (1)

anon
anon

Reputation:

The typical approach is to use an OR depending on whether the parameter was supplied:

WHERE (Value = @Value OR @Value IS NULL);

However, this can be problematic, because you get exactly one execution plan for this query based on the first call. This means that if the first call specifies @Value = 1, and this leads to an index seek, the second call is going to get an index seek when the parameter is NULL (and you're not going to like the performance of that). Similarly in the other direction, the first parameter is NULL, you're going to get a scan because you're returning the whole table, but then on the second call when you just want one (or very few) rows, you're still going to get a scan.

The typical reaction to that is "add OPTION (RECOMPILE);"!

Which is great, except if Value is unique, or always has a very small number of any given value, and you are constantly calling the query with explicit values that lead to a small seek, you're recompiling that query every time for nothing. And every time you need the scan, you're recompiling that query every time for nothing also.

The compromise for this "kitchen sink" type of query with optional search parameters is to use dynamic SQL (and it's important to do this in a non-injectable way):

DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.TestTable';

IF @Value IS NOT NULL
BEGIN
  SET @sql += N' WHERE Value = @Value';
END

SET @sql += N';';

EXEC sys.sp_executesql @sql, N'@Value varchar(50)', @Value;

Now you have two different queries that will perform predictably in the two different scenarios and don't need to be recompiled. If you have data that can skew (some values of Value that would work better as a scan or different plan shape), then you can recompile just in that case:

  SET @sql += N' WHERE Value = @Value OPTION (RECOMPILE)';

You can even do this if you have special values you know do or don't lead to the problem, or if you have multiple parameters and some go against unique columns and others don't.

More info:

Upvotes: 4

Related Questions