Question

/* Q1. (20 points)Create a stored procedure sp_Q1 that takes two country names like 'Japan' or 'USA'as two inputs and returns two independent sets of rows: (1) all suppliers in the input countries, and (2) products supplied by these suppliers. The returned suppliers should contain SupplierID, CompanyName, Phone, and Country. The returned products require their ProductID, ProductName, UnitPrice, SupplierID, and must sorted by SupplierID.You should include 'go' to indicate the end of script that creates the procedure and you must include a simple testing script to call your sp_Q1 using 'UK' and 'Canada' as its two inputs\.\**For your reference only: A sample solution shows this procedure can be created in11 to 13 lines of code. Depending on logic and implementation or coding style, yoursolution could be shorter or longer\.\*/

Answer 1

Answer:

See the program code at explaination

Explanation:

CREATE PROCEDURE sp_Q1

atcountry1 NVARCHAR(15),

atcountry2 NVARCHAR(15)

AS

BEGIN

BEGIN

SELECT SupplierID, CompanyName, Phone, Country FROM suppliers

where Country in (atcountry1,atcountry2)

SELECT ProductID, ProductName, UnitPrice, SupplierID FROM Products

where SupplierID in(

SELECT SupplierID FROM suppliers

where Country in (atcountry1,atcountry2)) ORDER BY SupplierID

END

END

GO

-- Testing script.

DECLARE atRC int

DECLARE atcountry1 nvarchar(15)

DECLARE atcountry2 nvarchar(15)

-- Set parameter values here.

set atcountry1='UK'

set atcountry2='Canada'

EXECUTE atRC = [dbo].[sp_Q1]

atcountry1

,atcountry2

GO

Note: please kindly replace all the "at" with the correct at symbol.

The editor doesn't support it on Brainly.