时间戳

首页 > 解决方案 > 如何避免sqlserver中声明变量中的空条件

sql-server - 如何避免sqlserver中声明变量中的空条件

问题描述

CREATE TABLE #UserCompany
    ([ID] int, [CustId] varchar(2), [CustName] varchar(1), [Status] int)
;

INSERT INTO #UserCompany
    ([ID], [CustId], [CustName], [Status])
VALUES
    (1, 'a1', 'A', null),
    (2, 'a1', 'A', null),
    (3, 'a2', 'B', null),
    (4, 'a3', 'B', null),
    (5, 'a4', 'C', null),
    (6, 'a4', 'C', null),
    (7, 'a4', 'D', null),
    (8, 'a6', 'E', null)
;select * from #UserCompany

在传递一些值时，我正在获取值

declare @id varchar(100)='1,2'
select * from #UserCompany where id in (select  CAST(value AS int)  FROM string_SPLIT(@id, ','))

如果 iam 传递 null iam 没有得到任何值，即使我们没有传递任何值，如下所示

declare @id varchar(100)=null
select * from #UserCompany where id in (select  CAST(value AS int)  FROM string_SPLIT(@id, ','))

输出应该是总表应该显示

标签： sql-servertsqlsql-server-2016

解决方案

declare @id varchar(100)='1,2'

select * 
from #UserCompany 
where id in (select  CAST(value AS int)  FROM string_SPLIT(@id, ','))
   OR @id IS NULL;

推荐阅读