Use the two diagram below Northwind and Hospital data models diagrams to answer

Question

Use the two diagram below Northwind and Hospital data models diagrams to answer the following questions

1. Write a strored procedure that will receive two date values as parameters and return the customer company names, order ID values, and order dates for all orders placed during that period.

2. write a stored procedure that will receive all values needed for a new supplier except supplierID, create new supplier record, and return the value of the new record's supplierID in a RETURN statement. Set up the SP so that the default value for contry is 'USA.'

3. NORTHWIND: write a stored procedure that will receive A category name parameter and a country and return all product information for products in that category produced by suppliers from that country.

4. NORTHWIND: write a stored procedure that will receive all values needed for a new product except productID, create new product record, and return the value of the new record's productID in a RETURN statement. Set up the SP so that the default value for discontinued is 0 (ZERO).

1. Hospital Schedule Diagram Below

2. NorthWind Diagram Below

Bed Hat Room BedCount Bed Type Room Type occupied (y/n) DepartmentID Has Bed Assignment Has Admission Patient ID End Time StartTime BedNum EndTime Room Num Reason AdmissionID Admit PhysicianID Hass Orders Ha Patient Physician LastName LastName FirstName FirstName Gender Gender DOB LicenseNum Orders Has Procedure Order Type Procedure Name PatientID Description Has order Type PhysicianID OrderDate ProcedureID Lab Test Type LabTestID LabTestlD TestName Description Has Has Vitalsigns LabResults Performorder TestTime OrderID Results Results Performed ByID Performed ByID Performed ByID PerformTime OrderID Order ID Department DeptName Office Number CurrentMgrID Has Shift DepartmentID StartTime EndTime Has ShiftJob Quantity Has ShiftAssign JobTypelD Arrive Time DepartTime Takes Ha Has Ha Perform EmpAssignment EmployeeID DepartmentID JobTypeID EndDate Has Job Type JobName Description Has EmpJobQual StartDate EndDate Has Employee Last Name FirstName DateOf Birth Gender Title Has Manages

Explanation / Answer

1)

CREATE PROCEDURE dbo.SP_GetOrderDetails(
@startdate VARCHAR(20),
@enddate VARCHAR(20)
)
AS
BEGIN
SELECT cs.CompanyName,od.OrderID,od.OrderDate FROM Orders od INNER JOIN Customers cs ON cs.CustomerID=od.CustomerID
where convert(date,od.OrderDate)>=@startDate and convert(date,od.OrderDate)<=@endDate
END
/*
EXEC [NorthWind].[dbo].[SP_GetOrderDetails] '01/12/2016','12/12/2017'
*/

2)

CREATE PROCEDURE dbo.SP_InsertSupplierData(
@CompanyName NVARCHAR(40),
@ContactName NVARCHAR(30),
@ContactTitle NVARCHAR(30),
@Address NVARCHAR(60)   ,
@City NVARCHAR(15)       ,
@Region NVARCHAR(15)   ,
@PostalCode NVARCHAR(10) ,
@Country NVARCHAR(15)   ,
@Phone NVARCHAR(24)   ,
@Fax NVARCHAR(24)       ,
@HomePage NTEXT
)
AS
BEGIN
DECLARE @ID INT;
SET @ID=(SELECT MAX(SupplierID) FROM NorthWind.dbo.Suppliers) + 1;
INSERT INTO NorthWind.dbo.Suppliers VALUES(
@CompanyName   ,
@ContactName   ,
@ContactTitle   ,
@Address   ,
@City   ,
@Region   ,
@PostalCode   ,
isnull(@Country,'USA'),
@Phone   ,
@Fax   ,
@HomePage
)
SELECT * FROM NorthWind.dbo.Suppliers WHERE SupplierID=@ID;

RETURN @ID;
END

3)

CREATE PROCEDURE dbo.SP_GetProductInfo(
@CategoryName VARCHAR(15),
@Country VARCHAR(15)
)
AS
BEGIN
SELECT pd.* FROM Products pd INNER JOIN Categories ct ON ct.CategoryID=pd.CategoryID
INNER JOIN Suppliers sp ON sp.SupplierID=pd.SupplierID WHERE ct.CategoryName=@CategoryName AND sp.Country=@Country;
END

4)

CREATE PROCEDURE dbo.SP_InsertProductData(
@ProductName   varchar(40),
@SupplierID       int,
@CategoryID       int,
@QuantityPerUnit varchar(20),
@UnitPrice       money,
@UnitOnStock   smallint,
@UnitOnOrder   smallint,
@ReorderLevel   smallint,
@Discontinued    BIT)
AS
BEGIN
--ProductID
DECLARE @ProductID INT;
SET @ProductID=(SELECT MAX(ProductID) FROM NorthWind.dbo.Products)+1;
INSERT INTO NorthWind.dbo.Products VALUES(
@ProductName,  
@SupplierID   ,  
@CategoryID   ,  
@QuantityPerUnit ,
@UnitPrice       ,
@UnitOnStock   ,
@UnitOnOrder   ,
@ReorderLevel   ,
isnull(@Discontinued,0)
)
SELECT * FROM NorthWind.dbo.Products WHERE ProductID=@ProductID;
RETURN @ProductID;
END  

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.