小问题！！！

Question

CREATE PROCEDURE up_SearchBookOR
(
@BookName nvarchar(50),
@BookNum nvarchar(50),
@BookSubCategory nvarchar(50),
@BookAuthor nvarchar(50),
@BookPublisher nvarchar(50)
)
ASSELECT  BookCategory_TB.BookCategoryName, 
       BookSubCategory_TB.BookSubCategoryName,  BookInfo_TB.BookName, 
       BookInfo_TB.BookNumber,  ValidStatus_TB.ValidStatusName, 
       BookInfo_TB.Author,  BookInfo_TB.Publish,BookInfo_TB.BookID
FROM  BookInfo_TB INNER JOIN
       BookSubCategory_TB ON 
       BookInfo_TB.BookSubCategoryID =  BookSubCategory_TB.BookSubCategoryID
       INNER JOIN
       BookCategory_TB ON 
       BookSubCategory_TB.BookCategoryID =  BookCategory_TB.BookCategoryID INNER
       JOIN
       ValidStatus_TB ON 
       BookInfo_TB.ValidStatusID =  ValidStatus_TB.ValidStatusID
WHERE 
BookInfo_TB.BookName LIKE '%' + @BookName + '%' 
OR
BookInfo_TB.BookNumber LIKE '%' + @BookNum + '%'
        if @BookSubCategory <> "全部"
         begin
这儿错了 OR BookCategory_TB.BookCategoryName LIKE '%' + @BookSubCategory + '%'
         end
OR
BookInfo_TB.Author LIKE '%' + @BookAuthor + '%'
OR
BookInfo_TB.Publish LIKE '%' + @BookPublisher + '%'

Answer 1

if @BookSubCategory <> "全部"
         begin
OR BookCategory_TB.BookCategoryName LIKE '%' + @BookSubCategory + '%'
         end
不能在查询条件中出现。

Answer 2

改成：
WHERE 
BookInfo_TB.BookName LIKE '%' + @BookName + '%' 
OR
BookInfo_TB.BookNumber LIKE '%' + @BookNum + '%'
        OR 
        BookCategory_TB.BookCategoryName LIKE '%' + @BookSubCategory + '%' and @BookSubCategory <> "全部"
         OR
BookInfo_TB.Author LIKE '%' + @BookAuthor + '%'
OR
BookInfo_TB.Publish LIKE '%' + @BookPublisher + '%'

Answer 3

BookCategoryName在表中存放的是书的类别！
我现在要实现的功能是，如果@BookSubCategory的值为‘全部’那么我搜索的时，所有的类别都显示出来！
如果@BookSubCategory不是’全部‘，就按照类别显示！

Answer 4

应该是WHERE 
BookInfo_TB.BookName LIKE '%' + @BookName + '%' 
OR
BookInfo_TB.BookNumber LIKE '%' + @BookNum + '%'
        OR 
        BookCategory_TB.BookCategoryName LIKE '%' + @BookSubCategory + '%' 
         OR BookCategory_TB.BookCategoryName like '%全部%'
         OR
BookInfo_TB.Author LIKE '%' + @BookAuthor + '%'
OR
BookInfo_TB.Publish LIKE '%' + @BookPublisher + '%'

Answer 5

CREATE PROCEDURE up_SearchBookOR
(
@BookName nvarchar(50),
@BookNum nvarchar(50),
@BookSubCategory nvarchar(50),
@BookAuthor nvarchar(50),
@BookPublisher nvarchar(50)
)
ASSELECT  BookCategory_TB.BookCategoryName, 
       BookSubCategory_TB.BookSubCategoryName,  BookInfo_TB.BookName, 
       BookInfo_TB.BookNumber,  ValidStatus_TB.ValidStatusName, 
       BookInfo_TB.Author,  BookInfo_TB.Publish,BookInfo_TB.BookID
FROM  BookInfo_TB INNER JOIN
       BookSubCategory_TB ON 
       BookInfo_TB.BookSubCategoryID =  BookSubCategory_TB.BookSubCategoryID
       INNER JOIN
       BookCategory_TB ON 
       BookSubCategory_TB.BookCategoryID =  BookCategory_TB.BookCategoryID INNER
       JOIN
       ValidStatus_TB ON 
       BookInfo_TB.ValidStatusID =  ValidStatus_TB.ValidStatusID
WHERE 
BookInfo_TB.BookName LIKE '%' + @BookName + '%' 
OR
BookInfo_TB.BookNumber LIKE '%' + @BookNum + '%'
        or BookCategory_TB.BookCategoryName like case when @BookSubCategory<>'全部' then '%' + @BookSubCategory + '%' else BookCategory_TB.BookCategoryName end
OR
BookInfo_TB.Author LIKE '%' + @BookAuthor + '%'
OR
BookInfo_TB.Publish LIKE '%' + @BookPublisher + '%'