sql-server - 如何在不更改查询的情况下忽略查询中的参数

Question

我有一个从 SSRS 报告数据集复制的查询，例如包含 3 个参数。在实际情况中，我有很多大约 30 个参数。我想在 SSMS 中测试此查询并使用我自己的值处理参数。但我想填充查询一个参数 - @par1，其他参数必须具有所有值。

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

Answer 1

我想这对你有用

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