|
???????? 在数据库查询时，需要对数据类型进行转化，如我在建表是声明一个字段为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+'%'???????????
???????????????????????????????????????????????????????????????? AND??CONVERT? (NVARCHAR(1000),DESCRIPTION) LIKE '%'+@DESCRIPTION+'%'
??????????????????????????????END
????????? END??
代码可以考到SQLServer中进行，那样显示更清晰些，排版也好些：
第二种方法：
????? ALTER PROC [dbo].[NET_SELECT_SERVICE_LEVEL]
(
?@PACKAGE_LEVEL_ID INT,
?@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) 从长度上就看出第二种简单不少。第二种要注意的是，在字符串连接时，需要将所有不是字符串的类型的全部转化成字符串，包含int类型的数字。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
