User Tools

Site Tools


notes:sql:bookstore_sprocs

Bookstore - Stored Procedures

/* ***********************************************************
    Stored procedure: tar_GetBooksBySearchWords
    Created: 2008-10-17
    Author: Leszek
 
    Description: Retrieves books from the catalog taking into account page number.
    Books are ordered by publishing date (descending) and by title (ascending).
 
    Assumptions:
    Search words are separated by a single space.
 
    Input: 
    @SearchWords - a list of search words separated by a single space 
                   (all words: max. 1000 chars, single word: max. 50 chars)
    @Separator - a search word list delimiter
    @PageSize - the number of items on a page (between 1 and 100)
    @PageNo - the current page number (must be greater than 0)
    @ShowNotReleased - determines if the books not yet released should be shown
 
    @SortOrder:
    Remarks: Sort orders greater than 100 are available only for Admins.
 
    1 - Publication date [new to old] (the default sorting order)
        PubDate DESC
 
    2 - Popularity [rating * number of ratings] [high to low] (compare to the Amazon’s Bestselling order)
        Rating*NumRatings DESC, PubDate DESC
 
    3 - Rating [high to low], NumRatings [high to low], PubDate [new to old]
        Rating DESC, NumRatings DESC, PubDate DESC
 
    4 - Rating [low to high], NumRatings [high to low], PubDate [new to old]
        Rating ASC, NumRatings DESC, PubDate DESC
 
    5 - Price [high to low], PubDate [new to old]
        Price DESC, PubDate DESC
 
    6 - Price [low to high], PubDate [new to old]
        Price ASC, PubDate DESC
 
    100 - Date added [new to old]
          DateAdded DESC
 
    101 - Date added [old to new]
          DateAdded ASC
 
    Output:
    @TotalItems - number of all books on all pages
 
    Example:
    - @SortOrder=3 represents sorting by Rating DESC, NumRatings DESC, PubDate DESC.
    - REPLICATE pads the numerical fields with 0s
    - All columns must be textual in order to concatenate them.
 
    ORDER BY
    CASE WHEN @SortOrder=3 THEN REPLICATE('0', 5-LEN(CONVERT(VARCHAR(5), Rating))) + 
                                (CONVERT(VARCHAR(5), Rating)) + 
	                        REPLICATE('0', 5-LEN(CONVERT(VARCHAR(5), NumRatings))) + 
                                (CONVERT(VARCHAR(5), NumRatings)) +
                                CONVERT(CHAR(30), PubDate, 121) END DESC,
 
     Remarks:
     "Filters imposed by sorting order" filter out books with no user rating (when the sorting order involves 
     user ratings) and books with no price i.e. books available from sellers other than Amazon (when the sorting
     order involves the book price).
************************************************************** */
IF OBJECT_ID('tar_GetBooksBySearchWords', 'P') IS NOT NULL DROP PROC tar_GetBooksBySearchWords
GO
CREATE PROC tar_GetBooksBySearchWords
    @SearchWords NVARCHAR(1000),
    @PageSize INT,
    @PageNo INT,
    @ShowNotReleased BIT,
    @SortOrder TINYINT,
    @BookStatus INT = NULL,
    @TotalItems INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @SearchWordTable TABLE (Word NVARCHAR(50))
    INSERT INTO @SearchWordTable SELECT * FROM tar_SplitString(@SearchWords, ' ')
 
    DECLARE @StartRow INT, @EndRow INT
    SET @StartRow = (@PageNo-1) * @PageSize + 1
    SET @EndRow = (@PageNo-1) * @PageSize + @PageSize
 
    -- count the total number of records on all pages
    SELECT @TotalItems = COUNT(*) 
    FROM (SELECT sw.BookId
          FROM SearchWord sw
          JOIN @SearchWordTable st ON st.Word=sw.Word 
          JOIN Book bk ON sw.BookId=bk.BookId
          -- Implementation of a dynamic filter "Show Books Not Yet Released".
          WHERE PubDate <= CASE WHEN @ShowNotReleased=0 THEN GETDATE() ELSE PubDate END
          -- Filters imposed by sorting order
          AND Rating*NumRatings <> CASE WHEN @SortOrder=4 THEN 0 ELSE -1 END
          AND Price <> CASE WHEN @SortOrder=6 THEN 0 ELSE -1 END
          AND BookStatus=COALESCE(@BookStatus, BookStatus)
          GROUP BY sw.BookId
          -- "all" keywords must be found among search words (not "any")
          -- DISTINCT eliminates duplications of keywords in, for example, Title or Category.
          -- Thanks to that we can use "=" to compare the number of found keywords instead of ">=".
          HAVING COUNT(DISTINCT sw.Word)=(SELECT COUNT(*) FROM @SearchWordTable)
         ) AS BooksWithCount
 
    -- apply paging
    SELECT BookId, PubId, CatId, PubName, CatName, 
           Title, Authors, Isbn10, Isbn13,
           PubDate, IsDayMissing, IsMonthMissing, 
           Price, Rating, NumRatings, NumPages, 
           AmazonBookLink, AmazonImageLink, 
           EBookPath, SyncKeyId, SyncKeyDesc, BookTypeId, Comment, DateAdded, DateUpdated,
           AmazonPublisher,
           BookStatus, IsAsin
    FROM (SELECT bk.BookId, bk.PubId, bk.CatId, pub.PubName, cat.CatName, 
                 Title, Authors, Isbn10, Isbn13,
                 PubDate, IsDayMissing, IsMonthMissing,
                 Price, Rating, NumRatings, NumPages, 
                 AmazonBookLink, AmazonImageLink, 
                 EBookPath, sk.SyncKeyId, sk.SyncKeyDesc, BookTypeId, Comment, DateAdded, DateUpdated,
                 pub.AmazonPublisher,
                 BookStatus, IsAsin,
                 ROW_NUMBER() OVER (ORDER BY CASE WHEN @SortOrder=1 THEN PubDate END DESC, 
                                             CASE WHEN @SortOrder=2 THEN Rating*NumRatings END DESC, 
                                             CASE WHEN @SortOrder=2 THEN PubDate END DESC,
                                             CASE WHEN @SortOrder=3 THEN Rating END DESC, 
                                             CASE WHEN @SortOrder=3 THEN NumRatings END DESC, 
                                             CASE WHEN @SortOrder=3 THEN PubDate END DESC,
                                             CASE WHEN @SortOrder=4 THEN Rating END ASC, 
                                             CASE WHEN @SortOrder=4 THEN NumRatings END DESC, 
                                             CASE WHEN @SortOrder=4 THEN PubDate END DESC,
                                             CASE WHEN @SortOrder=5 THEN Price END DESC, 
                                             CASE WHEN @SortOrder=5 THEN PubDate END DESC, 
                                             CASE WHEN @SortOrder=6 THEN Price END ASC, 
                                             CASE WHEN @SortOrder=6 THEN PubDate END DESC,
                                             CASE WHEN @SortOrder=100 THEN DateAdded END DESC,
                                             CASE WHEN @SortOrder=101 THEN DateAdded END ASC) AS RowNum
        FROM SearchWord sw
        JOIN @SearchWordTable st ON st.Word=sw.Word 
        JOIN Book bk ON sw.BookId=bk.BookId
        JOIN Publisher pub ON bk.PubId=pub.PubId
        JOIN Category cat ON bk.CatId=cat.CatId
        JOIN SyncKeyword sk ON bk.SyncKeyId=sk.SyncKeyId
        -- Filter "Show Books Not Yet Released".
        WHERE PubDate <= CASE WHEN @ShowNotReleased=0 THEN GETDATE() ELSE PubDate END
        -- Filters imposed by sorting order
        AND Rating*NumRatings <> CASE WHEN @SortOrder=4 THEN 0 ELSE -1 END
        AND Price <> CASE WHEN @SortOrder=6 THEN 0 ELSE -1 END
        AND BookStatus=COALESCE(@BookStatus, BookStatus)
        GROUP BY bk.BookId, bk.PubId, bk.CatId, pub.PubName, cat.CatName, 
                 Title, Authors, Isbn10, Isbn13,
                 PubDate, IsDayMissing, IsMonthMissing,
                 Price, Rating, NumRatings, NumPages, 
                 AmazonBookLink, AmazonImageLink, 
                 EBookPath, sk.SyncKeyId, sk.SyncKeyDesc, BookTypeId, Comment, DateAdded, DateUpdated,
                 pub.AmazonPublisher,
                 BookStatus, IsAsin
        HAVING COUNT(DISTINCT sw.Word)=(SELECT COUNT(*) FROM @SearchWordTable)
	) AS BooksWithRowNum
    WHERE RowNum BETWEEN @StartRow AND @EndRow
    ORDER BY CASE WHEN @SortOrder=1 THEN PubDate END DESC, 
             CASE WHEN @SortOrder=2 THEN Rating*NumRatings END DESC, 
             CASE WHEN @SortOrder=2 THEN PubDate END DESC,
             CASE WHEN @SortOrder=3 THEN Rating END DESC, 
             CASE WHEN @SortOrder=3 THEN NumRatings END DESC, 
             CASE WHEN @SortOrder=3 THEN PubDate END DESC,
             CASE WHEN @SortOrder=4 THEN Rating END ASC, 
             CASE WHEN @SortOrder=4 THEN NumRatings END DESC, 
             CASE WHEN @SortOrder=4 THEN PubDate END DESC,
             CASE WHEN @SortOrder=5 THEN Price END DESC, 
             CASE WHEN @SortOrder=5 THEN PubDate END DESC, 
             CASE WHEN @SortOrder=6 THEN Price END ASC, 
             CASE WHEN @SortOrder=6 THEN PubDate END DESC,
             CASE WHEN @SortOrder=100 THEN DateAdded END DESC,
             CASE WHEN @SortOrder=101 THEN DateAdded END ASC
    SET NOCOUNT OFF
END
GO
 
/*
-- Example
DECLARE @TotalItems INT, @TotalItemsOUT INT
SET @TotalItemsOUT=0
EXEC tar_GetBooksBySearchWords @SearchWords='c++', @PageSize=20, @PageNo=1, @ShowNotReleased=1, @SortOrder=4, @BookStatus=0, @TotalItems=@TotalItemsOUT OUTPUT
PRINT @TotalItemsOUT
*/
 
/* ***********************************************************
    Stored procedure: tar_GetBooksByCategory
    Created: 2008-11-09
 
    Description: Retrieves books from a given category.
 
    Input:
    @CatId - id of the category to retrieve books from; if not provided books from all categories are returned
    @PageSize - the number of items on a page (between 1 and 100)
    @PageNo - the current page number (must be greater than 0)
    @ShowNotReleased, @SortOrder - see the description and the remarks of tar_GetBooksBySearchWords
 
    Output:
    @TotalItems - number of all books on all pages
************************************************************** */
IF OBJECT_ID('tar_GetBooksByCategory', 'P') IS NOT NULL DROP PROC tar_GetBooksByCategory
GO
CREATE PROC tar_GetBooksByCategory
    @CatId INT = NULL,
    @PageSize INT,
    @PageNo INT,
    @ShowNotReleased BIT,
    @SortOrder TINYINT,
    @BookStatus INT = NULL,
    @TotalItems INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @StartRow INT, @EndRow INT
    SET @StartRow = (@PageNo-1)*@PageSize + 1
    SET @EndRow = (@PageNo-1)*@PageSize + @PageSize
 
    -- count the total number of records on all pages
    SELECT @TotalItems = COUNT(*) 
    FROM Book
    WHERE CatId=COALESCE(@CatId, CatId)
    -- implementation of a dynamic filter "Show Books Not Yet Released"
    AND PubDate <= CASE WHEN @ShowNotReleased=0 THEN GETDATE() ELSE PubDate END
    -- filters imposed by sorting order
    AND Rating*NumRatings <> CASE WHEN @SortOrder=4 THEN 0 ELSE -1 END
    AND Price <> CASE WHEN @SortOrder=6 THEN 0 ELSE -1 END
    AND BookStatus=COALESCE(@BookStatus, BookStatus)
 
    -- apply paging
    SELECT BookId, PubId, CatId, PubName, CatName, 
           Title, Authors, Isbn10, Isbn13,
           PubDate, IsDayMissing, IsMonthMissing, 
           Price, Rating, NumRatings, NumPages, 
           AmazonBookLink, AmazonImageLink, 
           EBookPath, SyncKeyId, SyncKeyDesc, BookTypeId, Comment, DateAdded, DateUpdated,
           AmazonPublisher, BookStatus, IsAsin
    FROM (SELECT bk.BookId, bk.PubId, bk.CatId, pub.PubName, cat.CatName, 
                 Title, Authors, Isbn10, Isbn13,
                 PubDate, IsDayMissing, IsMonthMissing,
                 Price, Rating, NumRatings, NumPages, 
                 AmazonBookLink, AmazonImageLink, 
                 EBookPath, sk.SyncKeyId, sk.SyncKeyDesc, BookTypeId, Comment, DateAdded, DateUpdated,
                 pub.AmazonPublisher, BookStatus, IsAsin,
                 ROW_NUMBER() OVER (ORDER BY CASE WHEN @SortOrder=1 THEN PubDate END DESC, 
                                             CASE WHEN @SortOrder=2 THEN Rating*NumRatings END DESC, 
                                             CASE WHEN @SortOrder=2 THEN PubDate END DESC,
                                             CASE WHEN @SortOrder=3 THEN Rating END DESC, 
                                             CASE WHEN @SortOrder=3 THEN NumRatings END DESC, 
                                             CASE WHEN @SortOrder=3 THEN PubDate END DESC,
                                             CASE WHEN @SortOrder=4 THEN Rating END ASC, 
                                             CASE WHEN @SortOrder=4 THEN NumRatings END DESC, 
                                             CASE WHEN @SortOrder=4 THEN PubDate END DESC,
                                             CASE WHEN @SortOrder=5 THEN Price END DESC, 
                                             CASE WHEN @SortOrder=5 THEN PubDate END DESC, 
                                             CASE WHEN @SortOrder=6 THEN Price END ASC, 
                                             CASE WHEN @SortOrder=6 THEN PubDate END DESC,
                                             CASE WHEN @SortOrder=100 THEN DateAdded END DESC,
                                             CASE WHEN @SortOrder=101 THEN DateAdded END ASC) AS RowNum
          FROM Book bk
          JOIN Publisher pub ON bk.PubId=pub.PubId
          JOIN Category cat ON bk.CatId=cat.CatId
          JOIN SyncKeyword sk ON bk.SyncKeyId=sk.SyncKeyId
          WHERE bk.CatId=COALESCE(@CatId, bk.CatId)
          -- implementation of a dynamic filter "Show Books Not Yet Released"
	  AND PubDate <= CASE WHEN @ShowNotReleased=0 THEN GETDATE() ELSE PubDate END
          -- filters imposed by sorting order
          AND Rating*NumRatings <> CASE WHEN @SortOrder=4 THEN 0 ELSE -1 END
          AND Price <> CASE WHEN @SortOrder=6 THEN 0 ELSE -1 END
          AND BookStatus=COALESCE(@BookStatus, BookStatus)
         ) AS BooksWithRowNum
    WHERE RowNum BETWEEN @StartRow AND @EndRow
    ORDER BY CASE WHEN @SortOrder=1 THEN PubDate END DESC, 
             CASE WHEN @SortOrder=2 THEN Rating*NumRatings END DESC, 
             CASE WHEN @SortOrder=2 THEN PubDate END DESC,
             CASE WHEN @SortOrder=3 THEN Rating END DESC, 
             CASE WHEN @SortOrder=3 THEN NumRatings END DESC, 
             CASE WHEN @SortOrder=3 THEN PubDate END DESC,
             CASE WHEN @SortOrder=4 THEN Rating END ASC, 
             CASE WHEN @SortOrder=4 THEN NumRatings END DESC, 
             CASE WHEN @SortOrder=4 THEN PubDate END DESC,
             CASE WHEN @SortOrder=5 THEN Price END DESC, 
             CASE WHEN @SortOrder=5 THEN PubDate END DESC, 
             CASE WHEN @SortOrder=6 THEN Price END ASC, 
             CASE WHEN @SortOrder=6 THEN PubDate END DESC,
             CASE WHEN @SortOrder=100 THEN DateAdded END DESC,
             CASE WHEN @SortOrder=101 THEN DateAdded END ASC
 
    SET NOCOUNT OFF
END
GO
 
/*
-- Example
DECLARE @TotalItems INT
EXEC tar_GetBooksByCategory NULL, 20, 1, 1, 1, @TotalItems OUTPUT
PRINT @TotalItems
*/
 
/* ***********************************************************
    Stored procedure: tar_GetBookByIsbn
    Created: 2009-11-03
 
    Description: Get information about a single book on the basis of its ISBN10 or ISBN13.
 
    Input: 
    @Isbn - the ISBN10 or ISBN13 of the requested book.
 
    Remarks:
    Although @TotalItems is always 1, it is required in order to keep
    the method consistent with other methods that retrieve data about books.
************************************************************** */
IF OBJECT_ID('tar_GetBookByIsbn', 'P') IS NOT NULL DROP PROC tar_GetBookByIsbn
GO
CREATE PROC tar_GetBookByIsbn
    @Isbn CHAR(13),
    @TotalItems INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON
 
    SELECT bk.BookId, bk.PubId, bk.CatId, pub.PubName, cat.CatName, 
           Title, Authors, Isbn10, Isbn13,
           PubDate, IsDayMissing, IsMonthMissing,
           Price, Rating, NumRatings, NumPages, 
           AmazonBookLink, AmazonImageLink, 
           EBookPath, sk.SyncKeyId, sk.SyncKeyDesc, BookTypeId, Comment, DateAdded, DateUpdated,
           pub.AmazonPublisher, BookStatus, IsAsin
    FROM Book bk
    JOIN Publisher pub ON bk.PubId=pub.PubId
    JOIN Category cat ON bk.CatId=cat.CatId
    JOIN SyncKeyword sk ON bk.SyncKeyId=sk.SyncKeyId
    WHERE Isbn13=@Isbn OR Isbn10=RTRIM(@Isbn)
 
    SELECT @TotalItems = @@ROWCOUNT
 
    SET NOCOUNT OFF
END
GO
 
/* ***********************************************************
    Stored procedure: tar_GetBookCount
    Created: 2009-10-01
 
    Description: Returns the number of all books from a given category.
 
    Input:
    @CatId - category id
    @ShowNotReleased, @SortOrder, @BookStatus - see the description and the remarks of tar_GetBooksBySearchWords
 
    Return value:
    The number of books from a given category.
 
    Remarks:
    -1 is used here as a special case. As a consequence Rating, NumRatings, and Price can't be less than 0.
************************************************************** */
IF OBJECT_ID('tar_GetBookCount', 'P') IS NOT NULL DROP PROC tar_GetBookCount
GO
CREATE PROC tar_GetBookCount
    @CatId INT = NULL,
    @ShowNotReleased BIT,
    @SortOrder TINYINT,
    @BookStatus INT = NULL
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @BookCount INT
 
    SELECT @BookCount = COUNT(*)
    FROM Book
    WHERE CatId=COALESCE(@CatId, CatId)
    -- implementation of a dynamic filter "Show Books Not Yet Released"
    AND PubDate <= CASE WHEN @ShowNotReleased=0 THEN GETDATE() ELSE PubDate END
    -- Filters imposed by sorting order
    AND Rating*NumRatings <> CASE WHEN @SortOrder=4 THEN 0 ELSE -1 END
    AND Price <> CASE WHEN @SortOrder=6 THEN 0 ELSE -1 END
    AND BookStatus=COALESCE(@BookStatus, BookStatus)
 
    RETURN @BookCount
 
    SET NOCOUNT OFF
END
GO
 
/* ***********************************************************
    Stored procedure: tar_GetCategories
    Created: 2008-11-07
 
    Description: Retrieves all book categories except the [No Category] item.
 
    Input: N/A
************************************************************** */
IF OBJECT_ID('tar_GetCategories', 'P') IS NOT NULL DROP PROC tar_GetCategories
GO
CREATE PROC tar_GetCategories
    @ReturnUndefinedCategory BIT = 0
AS
BEGIN
    SET NOCOUNT ON
 
    SELECT CatId, CatName, CatCounter 
    FROM Category 
    WHERE (CatId > 0 AND @ReturnUndefinedCategory=0)
    OR @ReturnUndefinedCategory=1
    ORDER BY CatName
 
    SET NOCOUNT OFF
END
GO
 
/* ***********************************************************
    Stored procedure: tar_AddUserKeywords
    Created: 2008-12-24
 
    Description: Stores keywords entered by a user.
************************************************************** */
IF OBJECT_ID('tar_AddUserKeywords', 'P') IS NOT NULL DROP PROC tar_AddUserKeywords
GO
CREATE PROC tar_AddUserKeywords
    @UserKeywords NVARCHAR(1000),
    @FormattedKeywords NVARCHAR(1000),
    @SessionId CHAR(20)
AS
BEGIN
    SET NOCOUNT ON
 
    INSERT INTO UserKeyword(UserKeywords, FormattedKeywords, SessionId)
           VALUES (@UserKeywords, @FormattedKeywords, @SessionId)
 
    SET NOCOUNT OFF
END
GO
 
--Example:
--EXEC tar_AddUserKeywords 'DirectX Game Programming', 'directx game programming', '23212345678234123009'
 
/* ***********************************************************
    Stored procedure: tar_IncreaseCategoryCounter
    Created: 2008-12-24
 
    Description: Increases the counter for a given category when a user selects it.
 
    Input:
    @CatId - Id of the category which counter needs to be increased.
************************************************************** */
IF OBJECT_ID('tar_IncreaseCategoryCounter', 'P') IS NOT NULL DROP PROC tar_IncreaseCategoryCounter
GO
CREATE PROC tar_IncreaseCategoryCounter
    @CatId INT
AS
BEGIN
    SET NOCOUNT ON
 
    UPDATE Category SET CatCounter=CatCounter+1 WHERE CatId=@CatId
 
    SET NOCOUNT OFF
END
GO
 
/* ***********************************************************
    Stored procedure: tar_AddBook
    Created: 2009-02-24
 
    Description: Determines if a book with the given ISBN10 already exists in the catalog and adds it if 
    it does not exist. Additionally publisher and category search words are assigned to the new book.
 
    Return value:
    The id of the new book or 0 is the book already exists.
 
    Remarks:
    By default the newly added book is private and its book type is "new".
    The tar_AddBook imposes some constraints on the maximum length of the search words.
    These restrictions must be taken into account in the business layer.
 
    Constraints:
    The max. length of all title search words including separators: 1000
    The max. length of all author search words including separators: 1000
    The max. length of a single word in a book title: 50
    The max. length of a single word in book authors: 50
************************************************************** */
IF OBJECT_ID('tar_AddBook', 'P') IS NOT NULL DROP PROC tar_AddBook
GO
 
CREATE PROC tar_AddBook
    @Title NVARCHAR(250), 
    @Authors NVARCHAR(520), 
    @Isbn10 CHAR(10), -- may be an ASIN
    @Isbn13 CHAR(13), 
    @PubDate DATETIME, 
    @IsDayMissing BIT, 
    @IsMonthMissing BIT,
    @Price MONEY,
    @Rating DECIMAL(2,1),
    @NumRatings INT,
    @NumPages INT,
    @AmazonBookLink VARCHAR(250),
    @AmazonImageLink VARCHAR(250),
    @TitleSearchWords NVARCHAR(1000),
    @AuthorsSearchWords NVARCHAR(1000),
    @PublisherSearchWords NVARCHAR(1000),
    @SyncKeyId INT, -- can be 0
    @DateAdded DATETIME,
    @IsAsin BIT,
    @AmazonPublisher VARCHAR(250)
AS
BEGIN
    SET NOCOUNT ON
 
    -- Return if the book already exists.
    IF EXISTS(SELECT BookId FROM Book WHERE Isbn10=@Isbn10)
    BEGIN
        -- Check if the book which has the same ISBN has also the same title.
        -- If it does, it means this book already exists in the catalog.
        -- If it does not, it means the ISBN10 is duplicated and ISBN13 should be used to identify books.
        -- The reason for this test is to make sure ISBN10 still identifies books uniquely.
        -- Amazon recommends using ISBN13 to identify books but we still use ISBN10 as some books
        -- in Amazon (such as ebooks) do not have ISBN13.
        IF EXISTS(SELECT BookId FROM Book WHERE Isbn10=@Isbn10 AND Title=@Title)
            -- The book already exists.
            RETURN 0 
        ELSE
        BEGIN
            -- The ISBN10 is the same but the title is different. It means that the ISBN is duplicated or
            -- that the book title has been changed. 
            -- Raise an error and provide an error code in the State parameter.
            DECLARE @OldTitle NVARCHAR(250)
            SELECT @OldTitle=Title FROM Book WHERE Isbn10=@Isbn10
 
            RAISERROR(N'Cannot add the book "%s" to the catalog. ' +
                'Another book with the same ISBN %s and the title "%s" already exists ' +
                 'or the title of the book has been changed.', 16, 1, @Title, @Isbn10, @OldTitle)
            RETURN
        END
    END
 
    BEGIN TRAN
 
    -- Resolve publisher.
    DECLARE @PubId INT
    EXEC @PubId = tar_GetPublisherIdByAmazonPublisher @AmazonPublisher
    IF @PubId = 0 EXEC @PubId = tar_AddPublisher @AmazonPublisher, @PublisherSearchWords
 
    -- Resolve category.
    DECLARE @CatId INT
    SELECT @CatId=DefaultCatId FROM SyncKeyword WHERE SyncKeyId=@SyncKeyId
 
    -- Split the book title and the book author search words. A single space is used as a word separator.
    DECLARE @TitleSearchWordTable TABLE (Word NVARCHAR(50))
    INSERT INTO @TitleSearchWordTable SELECT * FROM tar_SplitString(@TitleSearchWords, ' ')
    DECLARE @AuthorsSearchWordTable TABLE (Word NVARCHAR(50))
    INSERT INTO @AuthorsSearchWordTable SELECT * FROM tar_SplitString(@AuthorsSearchWords, ' ')
 
    DECLARE @BookId INT, @ERR INT
 
    -- add book
    INSERT INTO Book(PubId, CatId, Title, Authors, Isbn10, Isbn13, PubDate, IsDayMissing, IsMonthMissing, 
                Price, Rating, NumRatings, NumPages, AmazonBookLink, AmazonImageLink, SyncKeyId, DateAdded,
                DateUpdated, IsAsin, BookStatus, BookTypeId)
        SELECT @PubId, @CatId, @Title, @Authors, @Isbn10, @Isbn13, @PubDate, @IsDayMissing, @IsMonthMissing,
               @Price, @Rating, @NumRatings, @NumPages, @AmazonBookLink, @AmazonImageLink, @SyncKeyId, @DateAdded,
               @DateAdded, @IsAsin,
               1, 0 -- BookStatus=1 (New), BookTypeId=0 ([Not Specified])
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    SELECT @BookId = SCOPE_IDENTITY()
 
    -- add title search words (1-Book.Title)
    INSERT INTO SearchWord(BookId, SearchWordType, Word)
    SELECT @BookId, 1, Word FROM @TitleSearchWordTable
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    -- add authors search words (2-Book.Author)
    INSERT INTO SearchWord(BookId, SearchWordType, Word)
    SELECT @BookId, 2, Word FROM @AuthorsSearchWordTable
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    -- add ISBN-10 search words (3-Book.ISBN)
    INSERT INTO SearchWord(BookId, SearchWordType, Word)
    SELECT @BookId, 3, @Isbn10 
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    -- add ISBN-13 search words (3-Book.ISBN)
    INSERT INTO SearchWord(BookId, SearchWordType, Word)
    SELECT @BookId, 3, @Isbn13
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    -- add publisher search words (10-Publisher.PubName)
    INSERT INTO SearchWord(BookId, SearchWordType, Word)
    SELECT @BookId, 10, Word FROM PublisherSearchWord WHERE PubId = @PubId
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    -- add category search words (20-Category.CatName)
    INSERT INTO SearchWord(BookId, SearchWordType, Word)
    SELECT @BookId, 20, Word FROM CategorySearchWord WHERE CatId = @CatId
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    COMMIT TRAN
 
    SET NOCOUNT OFF
 
    RETURN @BookId
 
ErrorHandler:
    ROLLBACK TRAN
    RAISERROR('tar_AddBook sproc - transaction rollback', 1, 1)
END
GO
 
/* ***********************************************************
    Stored procedure: tar_UpdateBook
    Created: 2009-12-17
 
    Remarks:
    If @BookStatus is NULL the BookState value will remain unchanged.
************************************************************** */
IF OBJECT_ID('tar_UpdateBook', 'P') IS NOT NULL DROP PROC tar_UpdateBook
GO
CREATE PROC tar_UpdateBook
    @BookId INT,
    @CatId INT,
    @BookTypeId INT,
    @EBookPath VARCHAR(100),
    @Comment VARCHAR(250),
    @BookStatus INT = NULL
AS
BEGIN
    SET NOCOUNT ON
 
    UPDATE Book SET CatId=@CatId,
                BookTypeId=@BookTypeId,
                EBookPath=@EBookPath,
                Comment=@Comment,
                BookStatus=COALESCE(@BookStatus, BookStatus)
    WHERE BookId=@BookId
 
    SET NOCOUNT OFF
END
GO
 
/* ***********************************************************
    Stored procedure: tar_UpdateBookFromAmazonInfo
    Created: 2009-10-01
 
    Description: Updates a book on the basis of info retrieved from Amazon.
    The sproc also refreshes search words.
 
    Input: Sync info.
 
    Remarks:
    NULL for some input parameters indicates that the values have not changed.
 
    Constraints:
    The max. length of all title search words including separators: 1000
    The max. length of all author search words including separators: 1000
    The max. length of a single word in a book title: 50
    The max. length of a single word in book authors: 50
************************************************************** */
IF OBJECT_ID('tar_UpdateBookFromAmazonInfo', 'P') IS NOT NULL DROP PROC tar_UpdateBookFromAmazonInfo
GO
CREATE PROC tar_UpdateBookFromAmazonInfo
    @BookId INT,
    @PubId INT = NULL,
    @Title NVARCHAR(250) = NULL, 
    @Authors NVARCHAR(520) = NULL, 
    @Isbn13 CHAR(13) = NULL,
    @PubDate DATETIME, 
    @IsDayMissing BIT, 
    @IsMonthMissing BIT,
    @Price MONEY,
    @Rating DECIMAL(2,1),
    @NumRatings INT,
    @NumPages INT,
    @AmazonBookLink VARCHAR(250),
    @AmazonImageLink VARCHAR(250),
    @DateUpdated DATETIME,
    @TitleSearchWords NVARCHAR(1000) = NULL,
    @AuthorsSearchWords NVARCHAR(1000) = NULL,
    @IsAsin BIT
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @ERR INT
 
    BEGIN TRAN
 
    -- Update the book. Invoke an UPDATE trigger to insert old values to BookUpdate table.
    UPDATE Book SET PubDate=@PubDate, 
                IsDayMissing=@IsDayMissing, 
                IsMonthMissing=@IsMonthMissing, 
                Price=@Price, 
                Rating=@Rating, 
                NumRatings=@NumRatings, 
                NumPages=@NumPages, 
                AmazonBookLink=@AmazonBookLink, 
                AmazonImageLink=@AmazonImageLink,
                DateUpdated=@DateUpdated,
                IsAsin=@IsAsin
    WHERE BookId=@BookId
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    -- Update the fields that were specified explicitly as updated or not updated.
    IF @Title IS NOT NULL UPDATE Book SET Title=@Title WHERE BookId=@BookId
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    IF @Authors IS NOT NULL UPDATE Book SET Authors=@Authors WHERE BookId=@BookId
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    IF @PubId IS NOT NULL UPDATE Book SET PubId=@PubId WHERE BookId=@BookId
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    IF @Isbn13 IS NOT NULL UPDATE Book SET Isbn13=@Isbn13 WHERE BookId=@BookId
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    -- Refresh title search words (1-Book.Title)
    IF @TitleSearchWords IS NOT NULL 
    BEGIN
        DELETE FROM SearchWord WHERE BookId=@BookId AND SearchWordType=1
 
        SELECT @ERR = @@ERROR
        IF @ERR <> 0 GOTO ErrorHandler
 
        DECLARE @TitleSearchWordTable TABLE (Word NVARCHAR(50))
        INSERT INTO @TitleSearchWordTable SELECT * FROM tar_SplitString(@TitleSearchWords, ' ')
 
        INSERT INTO SearchWord(BookId, SearchWordType, Word)
        SELECT @BookId, 1, Word
        FROM @TitleSearchWordTable
 
        SELECT @ERR = @@ERROR
        IF @ERR <> 0 GOTO ErrorHandler
    END
 
    -- Refresh authors search words (2-Book.Author)
    IF @AuthorsSearchWords IS NOT NULL 
    BEGIN
        DELETE FROM SearchWord WHERE BookId=@BookId AND SearchWordType=2
 
        SELECT @ERR = @@ERROR
        IF @ERR <> 0 GOTO ErrorHandler
 
        DECLARE @AuthorsSearchWordTable TABLE (Word NVARCHAR(50))
        INSERT INTO @AuthorsSearchWordTable SELECT * FROM tar_SplitString(@AuthorsSearchWords, ' ')
 
        INSERT INTO SearchWord(BookId, SearchWordType, Word)
        SELECT @BookId, 2, Word
        FROM @AuthorsSearchWordTable
 
        SELECT @ERR = @@ERROR
        IF @ERR <> 0 GOTO ErrorHandler
    END
 
    -- Refresh ISBN13 search word (3-Book.Isbn)
    -- Note: Only ISBN13 is refreshed. ISBN10 remains the same. ISBN10 serves as a primary key.
    IF @Isbn13 IS NOT NULL
    BEGIN
        DELETE FROM SearchWord WHERE BookId=@BookId AND SearchWordType=3 AND LEN(Word)=13
 
        SELECT @ERR = @@ERROR
        IF @ERR <> 0 GOTO ErrorHandler
 
        INSERT INTO SearchWord(BookId, SearchWordType, Word)
        SELECT @BookId, 3, @Isbn13
 
        SELECT @ERR = @@ERROR
        IF @ERR <> 0 GOTO ErrorHandler
    END	
 
    -- Refresh publisher search words (10-Publisher.PubName)
    IF @PubId IS NOT NULL
    BEGIN
        DELETE FROM SearchWord WHERE BookId=@BookId AND SearchWordType=10
 
        SELECT @ERR = @@ERROR
        IF @ERR <> 0 GOTO ErrorHandler
 
        INSERT INTO SearchWord(BookId, SearchWordType, Word)
        SELECT @BookId, 10, Word
            FROM PublisherSearchWord
            WHERE PubId = @PubId
 
        SELECT @ERR = @@ERROR
        IF @ERR <> 0 GOTO ErrorHandler
    END
 
 
    COMMIT TRAN
 
    SET NOCOUNT OFF
 
    RETURN
 
ErrorHandler:
    ROLLBACK TRAN
    RAISERROR('tar_UpdateBookFromAmazonInfo sproc - transaction rollback', 1, 1)
END
GO
 
/* ***********************************************************
    Stored procedure: tar_GetPublisherIdByAmazonPublisher
    Created: 2009-02-25
 
    Description: Returns the publisher id on the basis of the provided
    publisher name. Used for lookup during adding new books from Amazon.
 
    Input:
    @PubName - publisher name
 
    Return value:
    Publisher id or 0 if the publisher not found.
************************************************************** */
IF OBJECT_ID('tar_GetPublisherIdByAmazonPublisher', 'P') IS NOT NULL DROP PROC tar_GetPublisherIdByAmazonPublisher
GO
CREATE PROC tar_GetPublisherIdByAmazonPublisher
    @AmazonPublisher NVARCHAR(250)
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @PubId INT
 
    SELECT @PubId = PubId
    FROM Publisher
    WHERE AmazonPublisher = @AmazonPublisher
 
    RETURN ISNULL(@PubId, 0)
 
    SET NOCOUNT OFF
END
GO
 
/* ***********************************************************
    Stored procedure: tar_AddPublisher
    Created: 2009-02-25
 
    Description: Creates a publisher record and corresponding search words.
 
    Input:
    @AmazonPublisher - Publisher name. This value is initially the same for DisplayName 
        (i.e. the publisher name displayed on the website) and AmazonPublisher (i.e. the exact name of 
        the publisher as on the Amazon website and used in lookups as a key)
    @PublisherSearchWords - A list of publisher search words separated by a single space.
 
    Return value:
    Id of the newly created publisher.
************************************************************** */
IF OBJECT_ID('tar_AddPublisher', 'P') IS NOT NULL DROP PROC tar_AddPublisher
GO
CREATE PROC tar_AddPublisher
    @AmazonPublisher NVARCHAR(250),
    @PublisherSearchWords NVARCHAR(500)
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @PubId INT, @ERR INT
 
    -- determine publisher search words
    DECLARE @SearchWordTable TABLE (Word NVARCHAR(50))
    INSERT INTO @SearchWordTable SELECT * FROM tar_SplitString(@PublisherSearchWords, ' ')
 
    BEGIN TRAN
 
    -- create publisher
    INSERT INTO Publisher(PubName, AmazonPublisher)
    VALUES(@AmazonPublisher, @AmazonPublisher)
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    SELECT @PubId = SCOPE_IDENTITY()
 
    -- add publisher search words
    INSERT INTO PublisherSearchWord(PubId, Word)
    SELECT @PubId, Word FROM @SearchWordTable
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    COMMIT TRAN
 
    SET NOCOUNT OFF
 
    RETURN @PubId
 
ErrorHandler:
    ROLLBACK TRAN
    RAISERROR('tar_AddPublisher sproc - transaction rollback', 1, 1)
END
GO
 
/* ***********************************************************
    Stored procedure: tar_AddCategory
    Created: 2009-03-01
 
    Description: Creates a new category and corresponding search words.
 
    Input:
    @CategoryName - category name
    @CategoryDesc - category description
    @CategorySearchWords - A list of category search words separated by a single space.
 
    Return value:
    Id of the newly created category.
************************************************************** */
IF OBJECT_ID('tar_AddCategory', 'P') IS NOT NULL DROP PROC tar_AddCategory
GO
CREATE PROC tar_AddCategory
    @CategoryName NVARCHAR(50),
    @CategoryDesc NVARCHAR(250),
    @CategorySearchWords NVARCHAR(500)
AS
BEGIN
    SET NOCOUNT ON
 
    DECLARE @CatId INT, @ERR INT
 
    -- split category search words
    DECLARE @SearchWordTable TABLE (Word NVARCHAR(50))
    INSERT INTO @SearchWordTable SELECT * FROM tar_SplitString(@CategorySearchWords, ' ')
 
    BEGIN TRAN
 
    -- create category
    INSERT INTO Category(CatName, CatDesc)
    VALUES(@CategoryName, @CategoryDesc)
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    SELECT @CatId = SCOPE_IDENTITY()
 
    -- add category search words
    INSERT INTO CategorySearchWord(CatId, Word)
    SELECT @CatId, Word FROM @SearchWordTable
 
    SELECT @ERR = @@ERROR
    IF @ERR <> 0 GOTO ErrorHandler
 
    COMMIT TRAN
 
    SET NOCOUNT OFF
 
    RETURN @CatId
 
ErrorHandler:
    ROLLBACK TRAN
    RAISERROR('tar_AddCategory sproc - transaction rollback', 1, 1)
END
GO
 
/* ***********************************************************
    Stored procedure: tar_GetSyncKeywords
    Created: 2009-08-26
 
    Description: Retrieves keywords for searching new books in Amazon.
 
    Input: N/A
************************************************************** */
IF OBJECT_ID('tar_GetSyncKeywords', 'P') IS NOT NULL DROP PROC tar_GetSyncKeywords
GO
CREATE PROC tar_GetSyncKeywords
AS
BEGIN
    SET NOCOUNT ON
 
    SELECT SyncKeyId, SyncKey, SyncKeyDesc, AmazonBrowseNodeId FROM SyncKeyword ORDER BY SyncKeyId
 
    SET NOCOUNT OFF
END
GO
 
/* ***********************************************************
    Stored procedure: tar_GetBookTypes
    Created: 2009-09-16
 
    Description: Retrieves book types.
 
    Input: N/A
************************************************************** */
IF OBJECT_ID('tar_GetBookTypes', 'P') IS NOT NULL DROP PROC tar_GetBookTypes
GO
CREATE PROC tar_GetBookTypes
AS
BEGIN
    SET NOCOUNT ON
 
    SELECT BookTypeId, BookTypeDesc FROM BookType ORDER BY BookTypeId
 
    SET NOCOUNT OFF
END
GO
 
/*
    GRANT permissions to sprocs
*/
 
GRANT EXEC ON tar_GetBooksBySearchWords TO webuser
GRANT EXEC ON tar_GetBooksByCategory TO webuser
GRANT EXEC ON tar_GetBookByIsbn TO webuser
GRANT EXEC ON tar_GetCategories TO webuser
GRANT EXEC ON tar_AddUserKeywords TO webuser
GRANT EXEC ON tar_IncreaseCategoryCounter TO webuser
GRANT EXEC ON tar_AddBook TO webuser
GRANT EXEC ON tar_UpdateBook TO webuser
GRANT EXEC ON tar_UpdateBookFromAmazonInfo TO webuser
GRANT EXEC ON tar_GetPublisherIdByAmazonPublisher TO webuser
GRANT EXEC ON tar_AddPublisher TO webuser
GRANT EXEC ON tar_AddCategory TO webuser
GRANT EXEC ON tar_GetSyncKeywords TO webuser
GRANT EXEC ON tar_GetBookTypes TO webuser
GRANT EXEC ON tar_GetBookCount TO webuser
notes/sql/bookstore_sprocs.txt · Last modified: 2020/08/26 (external edit)