CODEWITHSUNDEEP
sql-servert-sqlreporting-services
Vitaly Ascheulov
Vitaly Ascheulov

Reputation: 182

How to ignore parametr in query without chaging query

I have a query copied from dataset of SSRS report, with for example 3 parameters. In real situation I have many about 30 parameters. I want to test this query in SSMS and handle parameters with my own values. But I want fill to query one parameter - @par1, other must take have all values.

drop table testtable;
create table testtable(param1 int,param2 int,param3 int)
insert into testtable values 
(1,10,20),(2,11,21),(3,12,22);

--added parametrs to help query work
declare @par1 int;
declare @par2 int;
declare @par3 int;
set @par1=1;
set @par2= ? --what i need to put here to have only @par1 condition implemented in query
set @par3= ? --what i need to put here to have only @par1 condition implemented in query

-- Dataset Copyed from SSRS Report. And I dont want to delete anything from here, because query is complex in real situation.
select *
from testtable
where 
param1=@par1 and param2=@par2 and param3=@par3

Upvotes: 2

Views: 103

Answers (4)

Fuzzy
Fuzzy

Reputation: 3810

This should work:

select *
from testtable
where  ISNULL(@par1,0) IN (param1,0)
  and ISNULL(@par2,0) IN (param2,0)
  and ISNULL(@par3,0) IN (param3,0)

Upvotes: 0

shadow
shadow

Reputation: 1903

If you don't want ignore the parameters if they are Null try this:

select * from testtable
where 
( ( param1 = @par1 ) or ( param1 is null) )
and ( ( param2 = @par2 ) or ( param2 is null) )
and ( ( param3 = @par3 ) or ( param3 is null) )

Update

Just saw that you don't want to change the query. So that is not a solution. I can't come up with something.

Upvotes: 0

Ruslan K.
Ruslan K.

Reputation: 1981

It seems that there is no way to do what you want...

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93764

I guess this will work for you

select *
from testtable
where (param1=@par1 or @par1 is null) 
  and (param2=@par2 or @par2 is null) 
  and (param3=@par3 or @par3 is null)

Upvotes: 3

Related Questions