代码之家 › 专栏 › 技术社区 › Serdia

如果@Parameter=NULL,则选择所有行

sql-server-2017 tsql sql-server

0

Serdia · 技术社区 · 6 年前

@PolicySymbol ,它可以接受多个值。为此,我使用了一个split string函数: StringOfStringsToTable

@ControlNo ,那么我想把数据带来 controlNo 无论为哪个值设置 @政策符号 .

如果 =空,我想为

下面是代码示例:

create table #Test (ControlNo int, policy_symbol varchar(50))
insert into #Test values (1111, 'CSE'),
                         (2222, 'PRE'),
                         (3333, 'CSE'),
                         (4444, 'GLG'),
                         (4444, 'PRE'),
                         (4444, 'GLS')

declare
    @ControlNo int = 1111,
    @PolicySymbol varchar(50) = NULL 

SELECT 
DISTINCT ControlNo,policy_symbol
FROM #Test
WHERE ControlNo = COALESCE(@ControlNo, ControlNo)
 -- here, if parameter @PolicySymbol IS NULL I want to include all policy symbols
    AND policy_symbol IN (SELECT String FROM [dbo].[StringOfStringsToTable] (@PolicySymbol,','))

drop table #Test

1 回复 | 直到 6 年前

1

2

Dale K 6 年前

简单、合乎逻辑的 OR

WHERE ControlNo = COALESCE(@ControlNo, ControlNo)
-- If parameter @PolicySymbol IS NULL I want to include all policy symbols
AND (
    @PolicySymbol IS NULL
    OR policy_symbol IN (SELECT String FROM [dbo].[StringOfStringsToTable] (@PolicySymbol,','))
)