Reputation: 182
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
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
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
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