存储过程为参数NULL时的处理方法

准备一些数据：

 

set ansi_nulls on
go

set quoted_identifier on
go

create table [dbo].[goods](
    [projname] [nvarchar](10) null,
    [version] [nvarchar](10) null,
    [state] [nvarchar](3) null
) on [primary]
go


insert into  [dbo].[goods] ([projname],[version],[state])
values
(n'a项目',n'启动会版',n'已审核'),
(n'a项目',n'方案版',n'已审核'),
(n'a项目',n'施工图版',n'未审核'),
(n'b项目',n'    启动会版',n'未审核'),
(n'b项目',n'    方案版',n'未审核'),
(n'b项目',n'    施工图版',n'未审核')

go

 

先来看看下面2句sql语句，参数有值和null所查询到的结果：

 

declare @projname nvarchar(10) = n'a项目'
select [projname],[version],[state] from [dbo].[goods] where [projname] = @projname
go


declare @projname nvarchar(10) = null
select [projname],[version],[state] from [dbo].[goods] where [projname] = @projname
go

 

方法一：

 

declare @projname nvarchar(10) = n'a项目'
select [projname],[version],[state] from [dbo].[goods] where [projname] = @projname
go


declare @projname nvarchar(10) = null
select [projname],[version],[state] from [dbo].[goods] where [projname] =
case when @projname is null then [projname] else @projname end

go

 

方法二：

 

declare @projname nvarchar(10) = n'a项目'
select [projname],[version],[state] from [dbo].[goods] where [projname] = @projname
go


declare @projname nvarchar(10) = null
select [projname],[version],[state] from [dbo].[goods] where [projname] = @projname
 or @projname is null
go

 

方法三：

 

declare @projname nvarchar(10) = n'a项目'
select [projname],[version],[state] from [dbo].[goods] where [projname] = @projname
go


declare @projname nvarchar(10) = null
select [projname],[version],[state] from [dbo].[goods] where [projname] = 
iif(isnull(@projname, n'') = n'', [projname], @projname)
go

 

方法四：

 

declare @projname nvarchar(10) = n'a项目'
select [projname],[version],[state] from [dbo].[goods] where [projname] = @projname
go


declare @projname nvarchar(10) = null
select [projname],[version],[state] from [dbo].[goods] where [projname] = 
@projname or isnull(@projname, n'') = n''
go

 

方法五：

 

 

declare @projname nvarchar(10) = n'a项目'
select [projname],[version],[state] from [dbo].[goods] where [projname] = @projname
go


declare @projname nvarchar(10) = null
select [projname],[version],[state] from [dbo].[goods] where [projname] =
 iif(@projname is null, [projname], @projname)
go

 

方法六：

 

declare @projname nvarchar(10) = n'a项目'
select [projname],[version],[state] from [dbo].[goods] where [projname] = @projname
go

declare @projname nvarchar(10) = null

if len(isnull(@projname,'')) > 0
    select [projname],[version],[state] from [dbo].[goods]  where [projname] = @projname
else
    select [projname],[version],[state] from [dbo].[goods]
go