Question

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.

Answer 1

Check the explanation

Step-by-step explanation:

CREATE PROCEDURE sp_Q1

"at"country1 NVARCHAR(15),

"at"country2 NVARCHAR(15)

AS

BEGIN

BEGIN

SELECT SupplierID, CompanyName, Phone, Country FROM suppliers

where Country in ("at"country1,"at"country2)

SELECT ProductID, ProductName, UnitPrice, SupplierID FROM Products

where SupplierID in(

SELECT SupplierID FROM suppliers

where Country in ("at"country1,"at"country2)) ORDER BY SupplierID

END

END

GO

-- Testing script.

DECLARE "at"RC int

DECLARE "at"country1 nvarchar(15)

DECLARE "at"country2 nvarchar(15)

-- Set parameter values here.

set "at"country1='UK'

set "at"country2='Canada'

EXECUTE "at"RC = [dbo].[sp_Q1]

"at"country1

,"at"country2

GO

Kindly check the attached images below to see how it looks like on a code editor.