准备一些数据: ? 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'未审核') GOSource 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 GOSource 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 GOSource 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 GOSource 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) GOSource 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'' GOSource 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) GOSource 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] GOSource Code ? 来源:http://www./content-4-224651.html |
|