存储过程为参数NULL时的处理方法
准备一些数据:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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所查询到的结果:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
方法二:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
方法三:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
方法四:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
方法五:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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