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 ReviewsAdd ( @ProductID int, @CustomerName nvarchar(50), @CustomerEmail nvarchar(50), @Rating int, @Comments nvarchar(3850), @ReviewID int OUTPUT ) AS INSERT INTO Reviews ( ProductID, CustomerName, CustomerEmail, Rating, Comments ) VALUES ( @ProductID, @CustomerName, @CustomerEmail, @Rating, @Comments ) SELECT @ReviewID = @@IdentityDatabase Tables Used:
Reviews: The Reviews table has a many to one relationship to the Products table. The Reviews 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.