Stored Procedure with Data Validation
Write a script that creates and calls a stored procedure named spInsertProduct that inserts a row into the Products table. This stored procedure should accept five parameters. One parameter for each of these columns: CategoryID, ProductCode, ProductName, ListPrice, and DiscountPercent.
If the value for the ListPrice column is a negative number, the stored procedure should raise an error that indicates that this column doesn't accept negative numbers. Similarly, the procedure should raise an error if the value for the DiscountPercent column is a negative number.
When inserting data into the Products table, set the Description column to an empty string and set the DateAdded column to the current date.
Code at least two EXEC statements that test this procedure (one successful, one failure)
Write a script that creates and calls a stored procedure named spUpdateProductDiscount that updates the DiscountPercent column in the Products table. This procedure should have one parameter for the product ID and another for the discount percent.
If the value for the DiscountPercent column is a negative number, the stored procedure should raise an error that indicates that the value for this column must be a positive number.
Code at least two EXEC statements that test this procedure.
Trigger
Create a trigger named Products_INSERT that sets DateAdded column of the Products table to current date if the value for that column is null.
Test this trigger with the following
INSERT INTO Products (CategoryID, ProductCode, ProductName, Description, ListPrice, DiscountPercent) VALUES (1, 'G5122', 'Gretsch G5122 Double Cutaway Hollowbody', '', 999.99, 32);
Check and make sure the date was inserted with a SELECT statement.
Create a trigger named Products_UPDATE that checks the new value for the DiscountPercent column of the Products table. This trigger should raise an appropriate error if the discount percent is greater than 100 or less than 0.
If the new discount percent is between 0 and 1, this trigger should modify the new discount percent by multiplying it by 100. That way, a discount percent of .2 becomes 20.
Test this trigger with the following
UPDATE Products
SET DiscountPercent = .25
WHERE ProductID = 1;
Check to make sure the DiscountPercent is 25 with a SELECT statement.
Cursors
Write a script that creates a cursor for a SELECT query that consists of the ProductName and ListPrice columns for each product with a list price that's greater than $700. The rows in this result set should be sorted in descending sequence by list price. Then, the script should print the product name and list price for each product so it looks something like this:
Gibson SG, $2517.00
Gibson Les Paul, $1199.00
Write a script to declare and use a cursor for the following SELECT statement. Use a WHILE loop to fetch each row in the result set. Fetch each row into a set of local variables. Use the PRINT statement to return each row in the format "Name, $0.00" to the Messages tab.
SELECT LastName, AVG(ShipAmount) AS ShipAmountAvg
FROM Customers JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY LastName;