当前位置: 代码迷 >> Sql Server >> SQL模糊查询结合多条件判断的存储过程执行成功但无结果解决思路
  详细解决方案

SQL模糊查询结合多条件判断的存储过程执行成功但无结果解决思路

热度:84   发布时间:2016-04-27 14:01:56.0
SQL模糊查询结合多条件判断的存储过程执行成功但无结果
使用的代码如下:
ALTER PROCEDURE [dbo].[BOOK_frm_SearchBook](
@type char(100) = null,
@keywords char(30) = null,
@searchtype char(30) = null
)
AS
BEGIN

IF(@type = 'tab_QSearch')
BEGIN
IF(@searchtype = '图书名称')
BEGIN
SELECT
ltrim(rtrim(BOOK_Rec.BOOK_NAME)) As 图书名称,
ltrim(rtrim(BOOK_Rec.BOOK_NUM)) As 图书编码,
ltrim(rtrim(BOOK_Rec.BOOK_ISBN)) As 图书ISBN码, 
ltrim(rtrim(BOOK_TYPE.BOOK_TYPE_NAME)) As 图书类型名称, 
ltrim(rtrim(BOOK_Rec.BOOK_AUTHOR)) As 图书作者, 
ltrim(rtrim(BOOK_Rec.BOOK_PRESS)) As 图书出版社, 
BOOK_Rec.BOOK_PRESS_DATE As 图书出版日期, 
BOOK_Rec.BOOK_PRICE As 图书价格, 
BOOK_Rec.BOOK_PLACE As 图书存放位置,
BOOK_Rec.BOOK_MUCH As 图书库存数目, 
ltrim(rtrim(BOOK_Rec.BOOK_FLAG_BORROW)) As 图书可借与否,  
BOOK_Rec.BOOK_TEXT As 图书备注,
BOOK_INTRODUTION As 图书内容简介
FROM BOOK_Rec INNER JOIN
BOOK_TYPE ON BOOK_Rec.BOOK_TYPE_ID = BOOK_TYPE.BOOK_TYPE_ID
WHERE BOOK_Rec.BOOK_NAME LIKE [email protected]+'%'
END

ELSE IF(@searchtype = '图书作者')
BEGIN
SELECT
ltrim(rtrim(BOOK_Rec.BOOK_NAME)) As 图书名称,
ltrim(rtrim(BOOK_Rec.BOOK_NUM)) As 图书编码,
ltrim(rtrim(BOOK_Rec.BOOK_ISBN)) As 图书ISBN码, 
ltrim(rtrim(BOOK_TYPE.BOOK_TYPE_NAME)) As 图书类型名称, 
ltrim(rtrim(BOOK_Rec.BOOK_AUTHOR)) As 图书作者, 
ltrim(rtrim(BOOK_Rec.BOOK_PRESS)) As 图书出版社, 
BOOK_Rec.BOOK_PRESS_DATE As 图书出版日期, 
BOOK_Rec.BOOK_PRICE As 图书价格, 
BOOK_Rec.BOOK_PLACE As 图书存放位置,
BOOK_Rec.BOOK_MUCH As 图书库存数目, 
ltrim(rtrim(BOOK_Rec.BOOK_FLAG_BORROW)) As 图书可借与否, 
BOOK_INTRODUTION As 图书内容简介
FROM BOOK_Rec INNER JOIN
BOOK_TYPE ON BOOK_Rec.BOOK_TYPE_ID = BOOK_TYPE.BOOK_TYPE_ID
WHERE BOOK_Rec.BOOK_AUTHOR LIKE [email protected]+'%'
END
END

执行此存储过程如下:
BOOK_frm_SearchBook 'tab_QSearch','原理','图书名称'

执行结果:
执行命令成功,但结果集只有表头无表内容.

注:数据库中BOOK_Rec表中有图书名称为"马克思主义基本原理概论"/"振动粉碎理论及设备"等的书籍.


------解决方案--------------------
你把变量带进去,SQL语句直接执行下看有结果集没,调整看哪里有逻辑错误。
------解决方案--------------------
@type char(100) = null,
@keywords char(30) = null,
@searchtype char(30) = null

换成varchar试试
------解决方案--------------------
@keywords char(30) = null
char决定了里面有尾随空格
换成
@keywords varchar(30) = null
  相关解决方案