set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchString]
(
@Keywords VARCHAR(MAX),
@SearchType VARCHAR(100)
)
AS
DECLARE @SearchText VARCHAR(MAX)
DECLARE @Num INT
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @TableCount INT
DECLARE @CountSep INT
DECLARE @Count INT
DECLARE @BookId UNIQUEIDENTIFIER
DECLARE @AuthorName VARCHAR(500)
DECLARE @ISBN VARCHAR(50)
DECLARE @BookName VARCHAR(500)
DECLARE @PubYear VARCHAR(20)
BEGIN
IF @SearchType='General'
BEGIN
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='temp')
SET @TableCount = 1
ELSE
BEGIN
CREATE TABLE temp (BookID UNIQUEIDENTIFIER,AuthorName VARCHAR(500),
ISBN VARCHAR(50),BookName VARCHAR(500),PubYear VARCHAR(20))
SET @TableCount = 0
END
SELECT @CountSep = CHARINDEX(N':', @Keywords,1)
IF @CountSep = 0
BEGIN
SELECT BookID FROM (
SELECT DISTINCT Books.BookID AS BookID, Books.PublisherId, Books.IsDeleted FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN BookAuthor ON BookAuthor.BookId=Books.BookId INNER JOIN
Author ON Author.AuthorId=BookAuthor.AuthorId INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE BookPrice.ISBN like'%' + @Keywords + '%' OR
Books.BookName like'%' + @Keywords + '%'
OR Books.YearofPublication like'%' + @Keywords + '%'
OR Author.AuthorName like'%' + @Keywords + '%' AND Publisher.Publisher='XXX' ) AS tmpBook
INNER JOIN Publisher ON tmpBook.PublisherId=Publisher.PublisherId WHERE Publisher.Publisher='XXX'
AND tmpBook.IsDeleted = 0
END
ELSE
BEGIN
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@Keywords))
BEGIN
SELECT @NextPos = CHARINDEX(N':', @Keywords, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
BEGIN
SELECT @NextPos = LEN(@Keywords) + 1
END
SELECT @SearchText = RTRIM(LTRIM(SUBSTRING(@Keywords, @Pos, @NextPos - @Pos)))
IF @Pos = 1
BEGIN
DECLARE Outer_Cursor CURSOR FOR
SELECT BookID,AuthorName,ISBN,BookName,PubYear FROM (
SELECT DISTINCT Books.BookID AS BookID, Books.PublisherId,
Books.IsDeleted, Author.AuthorName AS AuthorName,
BookPrice.ISBN AS ISBN,
Books.BookName AS BookName, Books.YearofPublication AS PubYear
FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN BookAuthor ON BookAuthor.BookId=Books.BookId INNER JOIN
Author ON Author.AuthorId=BookAuthor.AuthorId INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE BookPrice.ISBN like'%' + @SearchText + '%' OR
Books.BookName like'%' + @SearchText + '%'
OR Books.YearofPublication like'%' + @SearchText + '%'
OR Author.AuthorName like'%' + @SearchText + '%' AND Publisher.Publisher='XXX' ) AS tmpBook
INNER JOIN Publisher ON tmpBook.PublisherId=Publisher.PublisherId WHERE Publisher.Publisher='XXX'
AND tmpBook.IsDeleted = 0
OPEN Outer_Cursor
FETCH NEXT FROM outer_cursor INTO @BookId, @AuthorName, @ISBN, @BookName, @PubYear
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO temp (BookId, AuthorName, ISBN, BookName, PubYear)
VALUES (@BookId, @AuthorName, @ISBN, @BookName, @PubYear)
FETCH NEXT FROM outer_cursor INTO @BookId, @AuthorName, @ISBN, @BookName, @PubYear
END
CLOSE outer_cursor
DEALLOCATE outer_cursor
END
ELSE
BEGIN
DELETE FROM temp WHERE BookID NOT IN (
SELECT BookID FROM temp
WHERE ISBN like'%' + @SearchText + '%' OR
BookName like'%' + @SearchText + '%'
OR PubYear like'%' + @SearchText + '%'
OR AuthorName like'%' + @SearchText + '%'
)
END
SET @Pos = @NextPos+1
END
SELECT BookID FROM temp AS tblRecordset
DROP TABLE temp
END
END
ELSE IF @SearchType='ISBN'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND BookPrice.ISBN like'%' + @Keywords + '%' AND BookPrice.IsDeleted=0
ELSE IF @SearchType='Title'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Books.BookName like'%' + @Keywords + '%' AND Books.IsDeleted=0
ELSE IF @SearchType='Author'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN BookAuthor ON BookAuthor.BookId=Books.BookId INNER JOIN
Author ON Author.AuthorId=BookAuthor.AuthorId INNER JOIN Publisher ON Books.PublisherId = Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Author.AuthorName like'%' + @Keywords + '%' AND BookAuthor.IsDeleted=0
ELSE IF @SearchType='PublicationYear'
SELECT DISTINCT Books.BookID AS BookID FROM Books INNER JOIN BookPrice ON Books.BookId=BookPrice.BookId
INNER JOIN Publisher ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Books.YearofPublication like'%' + @Keywords + '%' AND Books.IsDeleted=0
ELSE IF @SearchType='Subject'
SELECT DISTINCT BooksCategory.BookID AS BookID FROM
BooksCategory INNER JOIN Category ON BooksCategory.CategoryId=Category.CategoryId
INNER JOIN Books ON BooksCategory.BookId = Books.BookId INNER JOIN Publisher
ON Books.PublisherId=Publisher.PublisherId
WHERE Publisher.Publisher='XXX' AND Category.Category like'%' + @Keywords + '%' AND BooksCategory.IsDeleted=0
END
Post a Comment