Write a script that creates and calls a stored procedure

Assignment Help Basic Computer Science

Reference no: EM13766444

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;

Reference no: EM13766444

Questions Cloud

Explain the aida concept and how the sales team can help : Explain the AIDA concept and how the sales team can help support other elements of the marketing communications mix in moving potential clients through this process.

Problems based on motivational theory : Choose Maslow's, Herzberg's, or McClelland's model of motivation, and argue the primary reasons why it is applicable to the Kaluyu Memorial Hospital scenario.

Identify the various types of grains and grain-based product : Identify the various types of grains and grain-based products that contain the "whole" form of the grain. As part of this discussion, also consider some of the barriers to whole grain consumption.

Mistakes in processing account transactions : Identify the key steps in the closing process that provide the most opportunity to make mistakes in processing account transactions. Make at least two (2) recommendations for improving the accuracy and reliability of the information in the gaps th..

Write a script that creates and calls a stored procedure : 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

Explain the concept of federalism : Explain the concept of federalism; use specific examples of federalism and anti-federalism in your posting

Memorandum to the tax partner : Prepare a memorandum to the tax partner of your firm that discusses the transfer tax and income tax conequences of the proposed transactions described above. Also, make any recommendations that you deem appropriate.

Define what is the secure sockets layer : What is the Secure Sockets Layer (SSL) and how does it support e-commerce. What is the role of a CRM system. What sort of business benefits can such a system produce

Projects expectations-the company that i admire most : Each student is to evaluate a company that you believe is one of the best companies in the United States. There are several key attributes that you could use for determining your particular chosen company. There are also four key attributes of a b..

User Account

All Pages