This stored procedure adds an entry to the Reviews table for the product being reviewed. It returns the newly added ReviewID in an output parameter.
Definition:CREATE Procedure usp_ReviewsAdd ( @ProductID int, @CustomerName nvarchar(50), @CustomerEmail nvarchar(50), @Rating int, @Comments nvarchar(3850), @ReviewID int OUTPUT ) AS INSERT INTO Production.ProductReview ( ProductID, ReviewerName, EmailAddress, Rating, Comments ) VALUES ( @ProductID, @CustomerName, @CustomerEmail, @Rating, @Comments ); SELECT @ReviewID = SCOPE_IDENTITY();Database Tables Used:
ProductReview: The ProductReview table has a many to one relationship to the Product table. The ProductReview table contains all product reviews written by users. We decided not to create a relationship between CustomerName to the FullName in the Customers table to allow for anyone to review a product without logging into the system. The ratings used in our implementation range from 1 to 5 stars. The actual review text is allowed to be as large as 3850 characters.