CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
BenW
BenW

Reputation: 1453

SQL Server : Where condition based on the parameters

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.

Upvotes: 4

Views: 3481

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

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.

Upvotes: 3

BenW
BenW

Reputation: 1453

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 ?

Upvotes: 3

sqluser
sqluser

Reputation: 5672

Use CASE

select *,
(select field_1 from table_1 where field1='xx' and disty_name = case when @disty_name='All' then disty_name else @disty_name end) as field_1,
(select field_2 from table_2 where field1='xx' and disty_name = case when @disty_name='All' then disty_name else @disty_name end) as field_2,
from table_xx where disty_name = case when @disty_name='All' then disty_name else @disty_name end

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300559

One approach is

(select field_1 from table_1 
 where field1='xx' and (@disty_name = 'All' OR disty_name = @disty_name)) as field_1, 
 etc..

Be aware this might not generate an optimal plan. If you have many of these then using dynamic SQL to run one form or the other based on checking for 'All') might be a better approach.

Upvotes: 1

Related Questions