转载

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

正文到此结束

所属分类：编程开发

本文标签： 他山之玉
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。

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

热门推荐

相关文章

说给你听

本文目录

标签云

近期评论

网站信息