在数据库查询时,需要对数据类型进行转化,如我在建表是声明一个字段为DESCRIPTION设置类型为Ntext类型,那么我在查询是利用nvarchar类型的作为判断条件则将报错,这时我们需要对DESCRIPTION类型进行转化,使用CONVERT(NVARCHAR(N),DESCRIPTION)即可。
在编写查询SQL语句时,我会两种方式,一种是全用“逻辑形式”,另一种是使用字符串拼接。第一种更好操作易懂,但是查询条件增加到三个时就感觉很多了更不用说四个五个条件。第二种理解有那么一点点难,不过对付多条件查询很好用,对于查询,我更倾向于第二种方法。下面列出一个小项目里的例子:
项目要求,根据3个条件查询,第二个和第三个条件实现模糊查询。每个条件可有可无。
第一种方法:
CREATE PROC NET_SELECT_SERVICE_LEVEL
(
@PACKAGE_LEVEL_ID INT,
@SERVICE_LEVEL_NAME NVARCHAR(50),
@DESCRIPTION NVARCHAR(1000)
)
AS
IF @PACKAGE_LEVEL_ID<>0--当用户Id不为0
BEGIN
IF @SERVICE_LEVEL_NAME=''--服务等级为空,将出现两种情况
BEGIN
IF @DESCRIPTION=''--描述为空
SELECT * FROM T_SERVICE_LEVEL_YOURNAME WHERE PACKAGE_LEVEL_ID = @PACKAGE_LEVEL_ID
ELSE--描述不为空
SELECT * FROM T_SERVICE_LEVEL_YOURNAME WHERE PACKAGE_LEVEL_ID = @PACKAGE_LEVEL_ID AND
CONVERT(NVARCHAR(1000),DESCRIPTION) LIKE '%'+@DESCRIPTION+'%'
END
ELSE--服务等级不为空,则也有两种
BEGIN
IF @DESCRIPTION=''
SELECT * FROM T_SERVICE_LEVEL_YOURNAME WHERE PACKAGE_LEVEL_ID=@PACKAGE_LEVEL_ID AND
SERVICE_LEVEL_NAME=@SERVICE_LEVEL_NAME
ELSE
SELECT * FROM T_SERVICE_LEVEL_YOURNAME WHERE PACKAGE_LEVEL_ID=@PACKAGE_LEVEL_ID AND
SERVICE_LEVEL_NAME LIKE '%'+@SERVICE_LEVEL_NAME+'%' AND CONVERT(NVARCHAR(1000),DESCRIPTION)
LIKE '%'+@DESCRIPTION+'%'
END
END
ELSE--用户的ID为0
BEGIN
IF @SERVICE_LEVEL_NAME=''--服务为空
BEGIN
IF @DESCRIPTION=''
SELECT * FROM T_SERVICE_LEVEL_YOURNAME
ELSE
SELECT * FROM T_SERVICE_LEVEL_YOURNAME WHERE CONVERT(NVARCHAR(1000),DESCRIPTION) LIKE '%'+@DESCRIPTION+'%'
END
ELSE--服务不为空
BEGIN
IF @DESCRIPTION=''
SELECT * FROM T_SERVICE_LEVEL_YOURNAME WHERE SERVICE_LEVEL_NAME LIKE '%'+@SERVICE_LEVEL_NAME+'%'
ELSE
SELECT * FROM T_SERVICE_LEVEL_YOURNAME WHERE SERVICE_LEVEL_NAME LIKE '%'+@SERVICE_LEVEL_NAME+'%'
ANDCONVERT (NVARCHAR(1000),DESCRIPTION) LIKE '%'+@DESCRIPTION+'%'
END
END
代码可以考到SQLServer中进行,那样显示更清晰些,排版也好些:
第二种方法:
ALTER PROC [dbo].[NET_SELECT_SERVICE_LEVEL]
(
@PACKAGE_LEVEL_ID INT,
@SERVICE_LEVEL_NAME NVARCHAR(50),
@DESCRIPTION NVARCHAR(1000)
)
AS
DECLARE @SQL NVARCHAR(1500)
SET @SQL=''
IF @PACKAGE_LEVEL_ID <>0
SET @SQL=@SQL+'PACKAGE_LEVEL_ID='+CONVERT(NVARCHAR(20),@PACKAGE_LEVEL_ID)
IF @SERVICE_LEVEL_NAME <>''
BEGIN
IF @SQL=''
SET @SQL=@SQL+'SERVICE_LEVEL_NAME LIKE ''%'+@SERVICE_LEVEL_NAME+'%'''
ELSE
SET @SQL=@SQL+' AND SERVICE_LEVEL_NAME LIKE ''%'+@SERVICE_LEVEL_NAME+'%'''
END
IF @DESCRIPTION<>''
BEGIN
IF @SQL=''
SET @SQL=@SQL+'DESCRIPTION LIKE ''%'+@DESCRIPTION+'%'''
ELSE
SET @SQL=@SQL+' AND DESCRIPTION LIKE ''%'+@DESCRIPTION+'%'''
END
IF @SQL=''
EXEC('SELECT * FROM T_SERVICE_LEVEL_YOURNAME')
ELSE
EXEC('SELECT * FROM T_SERVICE_LEVEL_YOURNAME WHERE '+@SQL)