TSQL条件句

Question

我想在我的专栏上查个位置.

Where [Location 1] IN(@Brooklyn , @Queens)

但是如果没有指定位置，我想做这样的事情.

Where [Location 1]  = [Location 1]

换句话说，如果没有指定位置，我希望返回所有行。

这就是我到目前为止尝试过的& works...there必须是一种更好的方法。

Declare @Queens varchar(100)
Set @Queens = 'Queens'
Declare @Brooklyn varchar(100)
Set @Brooklyn = 'Brooklyn'

select * from AIDE_AVAILABILITY_REPORT 
where 
(
[LOCATION 1] = (Case When @Brooklyn is Not NULL Then 'Brooklyn' End) OR
[LOCATION 2] = (Case When @Brooklyn is Not NULL Then 'Brooklyn' End) OR

[LOCATION 1] = (Case When @Brooklyn is Null Then [LOCATION 1] End) OR
[LOCATION 2] = (Case When @Brooklyn is Null Then [LOCATION 2] End)

)
AND
(
[LOCATION 1] = (Case When @Queens is Not NULL Then 'Queens' End) OR
[LOCATION 2] = (Case When @Queens is Not NULL Then 'Queens' End) OR

[LOCATION 1] = (Case When @Queens is Null Then [LOCATION 1] End) OR
[LOCATION 2] = (Case When @Queens is Null Then [LOCATION 2] End)
)

Answer 1

在CASE表达式中，确保使用ELSE部件，而不是让它默认为NULL。这将为每组节省一行：

where 
(
[LOCATION 1] = (Case When @Brooklyn is Not NULL Then 'Brooklyn' ELSE [LOCATION 1] End) OR
[LOCATION 2] = (Case When @Brooklyn is Not NULL Then 'Brooklyn' ELSE [LOCATION 2] End)
)
AND
(
[LOCATION 1] = (Case When @Queens is Not NULL Then 'Queens' ELSE [LOCATION 1] End) OR
[LOCATION 2] = (Case When @Queens is Not NULL Then 'Queens' ELSE [LOCATION 2] End)
)

我会重新考虑你的变数。与其用每个区的名称命名变量，不如用一个带有区号的变量来命名：

WHERE [LOCATION 1] = (CASE WHEN @borough IS NULL THEN [LOCATION 1] ELSE @borough END) OR
      [LOCATION 2] = (CASE WHEN @borough IS NULL THEN [LOCATION 2] ELSE @borough END)

Answer 2

您可能需要考虑这样的方法：

if object_id('Locations') is not null drop table Locations

Create table Locations 
(
    location nvarchar(100) 
)

insert into Locations values('Brooklyn')
insert into Locations values('Queens')
insert into Locations values('Manhattan')
insert into Locations values('Bronx')
insert into Locations values('Staten Island')


Declare @locationlookup table 
(
    location nvarchar(100)
)

Declare @locations nvarchar(max)
Set @locations = ',Brooklyn,Queens,' 

if @locations <> '' 
    insert into @locationlookup 
        select location 
            from Locations 
                        where charindex(',' + location + ',', @locations) > 0  
else 
    insert into @locationlookup select location from Locations

select * from 
    AIDE_AVAILABILITY_REPORT a, @locationlookup b  
    where 
        a.location1 = b.location
        OR a.location2 = b.location         

Answer 3

每当我看到这样的问题时，我就会这样使用ISNULL：

SELECT * FROM AIDE_AVAILABILITY_REPORT 
WHERE ISNULL([Location 1],@Brooklyn) IN (@Brooklyn,@Queens) 
   OR ISNULL([Location 2],@Brooklyn) IN (@Brooklyn,@Queens) 

但是，您似乎认为变量可能为null (考虑到您的示例)，我会这样做：

SELECT * FROM AIDE_AVAILABILITY_REPORT 
WHERE [Location 1] IN (ISNULL(@Brooklyn,[Location 1],ISNULL(@Queens,[Location 1])
   OR [Location 2] IN (ISNULL(@Brooklyn,[Location 1],ISNULL(@Queens,[Location 1])