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 (SUPPLIERS TABLE) , and (2) products supplied by these suppliers (PRODUCTS TABLE). The returned suppliers should contain SupplierID, CompanyName, Phone, and Country. The returned products require their ProductID, ProductName, UnitPrice, SupplierID, and must be sorted by SupplierID. You must include a simple testing script to call your sp_Q1 using 'UK' and 'Canada' as its two inputs.

Answer 1

Answer:

Kindly note that, you're to replace "at" with shift 2 as the brainly text editor can't take the symbol

If you are executing this script with any other script, Add "GO" to the line above "CREATE PROCEDURE", on a line all by itself, and it should fix that error of 'CREATE/ALTER PROCEDURE'

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