SQL:声明多个变量，这些变量不会同时使用

Question

我正在编写一个查询，需要为其设置多个变量，但不能同时使用所有变量。它看起来像这样。

Declare @SuveryID as Int 
Declare @StateAbbrev as Varchar
Declare @InvStart as Date
Declare @InvEnd as Date
Set @SuveryID = ''
Set @StateAbbrev = ''
Set @InvStart = ''
Set @InvEnd = ''

我希望它做的是，只要调查ID中有值，其他ID就会被忽略。每当@StateAbbrev、@InvStart和@InvEnd有一个值时，@SurveryID就会被忽略。

Answer 1

一种方式是...

if @SuveryID  is null
begin
select *
from YourTable
where <all your other variables>
end
else
begin
select *
from YourTable
where Column = @SuveryID  
end

另一种方法是将变量设置为NULL@SuveryID is not null，然后使用捕获所有查询。这些被称为厨房水槽查询，Aaron Bertrand的那篇文章肯定值得一读。

if @SuveryID  is not null
begin
set @StateAbbrev  = null
set @InvStart  = null
<set all other variables to null>
end
select *
from YourTable
where (Column = @SuveryID or @SuveryID is null)
and   (Col2 = @StateAbbrev or @StateAbbrev  is null)
and   <continue for all variables>