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

存储过程为参数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 Source Code

 

先来看看下面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 Source Code

 

方法一：

 

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 Source Code

 

方法二：

 

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 Source Code

 

方法三：

 

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 Source Code

 

方法四：

 

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 Source Code

 

方法五：

 

 

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 Source Code

 

方法六：

 

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 Source Code