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

Answer:

Check the explanation

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.