SQL Server : Where condition based on the parameters

Question

I have a stored procedure as below. Please note this is an example only and the actual query is long and has many columns.

select 
    *,
    (select field_1 from table_1 where field1='xx' and disty_name = @disty_name) as field_1,
    (select field_2 from table_2 where field1='xx' and disty_name = @disty_name) as field_2,
from 
    table_xx 
where 
    disty_name = @disty_name

@disty_name parameter will pass some values and works fine.

My question is what is the best and shortest way to ignore disty_name = @disty_name condition if the @disty_name parameter contains the value 'All'

I just want to remove disty_name = @disty_name condition in some cases because user want to query all records without having disty_name filtered.

Answer 1

I think I found the answer..

Step 1 - make the parameter optional in the SP

@disty_name ncarchar(40) = null

and then in the query

select *,
(select field_1 from table_1 where field1='xx' and (@disty is null or dist_name=@disty))  as field_1,
(select field_2 from table_2 where field1='xx' and (@disty is null or dist_name=@disty)) as field_2,
from table_xx where (@disty is null or dist_name=@disty)

If you pass the @disty, it will filter the disty value from the query. If we have Null in the parameter , it will interpret as "Null is Null" which is true. If we have a parameter callrd 'xyz' it will interpret it as xyz is null which will return false. this is cool.. is it ?

Answer 2

Set @disty_name = NULLIF(@disty_name, 'All')

select *,
(select field_1 from table_1 where field1='xx' and disty_name = coalesce(@disty_name,disty_name)) as field_1,
(select field_2 from table_2 where field1='xx' and disty_name = coalesce(@disty_name,disty_name)) as field_2,
from table_xx where disty_name=coalesce(@disty_name,disty_name)

Also, I don't use it that often so I can't write it for you myself, but I suspect there's a more-efficient way to do this with UNIONs and a PIVOT.