56.SQLSERVER中字段，变量判断为空和空字符串

一、字段判断

（1）是否为空

select * from department where leader is NULL

select * from department where leader is not NULL

（2）是否为空字符‘’

select * from department where leader=''

select * from department where leader!=''

select * from department where leader<>''

二、变量判断

（1）是否为空

declare @r varchar(50)='abc'

if @r is null

begin

  set @r='暂无数据'

end

else if @r is not null

begin

  set @r='数据为：'+ @r

end

select @r //结果为：’数据为：’+abc

 begin 和 end 不写也是可以 

declare @r varchar(50)=NULL

if @r is null

  set @r='暂无数据'

else if @r is not null

  set @r='数据为：'+ @r

select @r //结果为：暂无数据

（2）是否为空字符‘’

declare @r varchar(50)='324'

if @r = ''

  set @r='暂无数据'

else if @r != ''

  set @r='数据为：'+ @r

select @r  //结果为：324

  

declare @r varchar(50)='bbb'

if LEN(@r) = 0

  set @r='暂无数据'

else if LEN(@r) != 0

  set @r='数据为：'+ @r

select @r