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)
