Write a stored procedure to do the query in problem 1, accepting the country name as a parameter. After testing the procedure, script it into a file for submission

Question 1 is: List the product ID, product name, and the total dollar amount for all items that were shipped to customers in the USA. Use a correlated query. Below is the query I created.

SELECT od.productid, p.productname, SUM(od.unitprice) FROM Sales.OrderDetails od, Production.Products p WHERE p.productid=od.productid AND od.orderid IN (SELECT s.orderid FROM Sales.Orders s WHERE s.shipcountry = 'USA' AND s.orderid = od.orderid) GROUP BY od.productid, p.productid, p.productname;

This is using the TSQLV4 schema below.

Sales OrderDetails PK,FK2 orderid PK,FK1 productid Sales Orders PK orderid HR.Employees PK empid Production Suppliers PK supplierid unitprice qty discount FK2 custid FK1 empid orderdate requireddate shippeddate FK3 shipperid freight shipname shipaddress shipcity shipregion shippostalcode shipcountry Sales OrderValues orderid custid empid shipperid orderdate requireddate shippeddate qty val lastname firstname title titleofcourtesy birth date hiredate address city region po stalcode country phone FK1 mgrid companyname contactname contacttitle address city region po stalcode country phone fax Production Products PK productid productname FK2 supplierid FK1 categoryid unitprice discontinued Sales EmpOrders empid ordermonth qty val nu morders Stats. Tests PK testid Sales Customers PK custid Sales Shippers PK shipperid companyname phone Production. Categories PK categoryid Sales.CustOrders custid ordermonth qty categoryname description dbo.Nums companyname contactname contacttitle address city region po stalcode country phone Stats Scores PKn PK,FK1 testid PK studentid Sales OrderTotalsBy Year orderyear qty score fax Sales OrderDetails PK,FK2 orderid PK,FK1 productid Sales Orders PK orderid HR.Employees PK empid Production Suppliers PK supplierid unitprice qty discount FK2 custid FK1 empid orderdate requireddate shippeddate FK3 shipperid freight shipname shipaddress shipcity shipregion shippostalcode shipcountry Sales OrderValues orderid custid empid shipperid orderdate requireddate shippeddate qty val lastname firstname title titleofcourtesy birth date hiredate address city region po stalcode country phone FK1 mgrid companyname contactname contacttitle address city region po stalcode country phone fax Production Products PK productid productname FK2 supplierid FK1 categoryid unitprice discontinued Sales EmpOrders empid ordermonth qty val nu morders Stats. Tests PK testid Sales Customers PK custid Sales Shippers PK shipperid companyname phone Production. Categories PK categoryid Sales.CustOrders custid ordermonth qty categoryname description dbo.Nums companyname contactname contacttitle address city region po stalcode country phone Stats Scores PKn PK,FK1 testid PK studentid Sales OrderTotalsBy Year orderyear qty score fax