Assignment 9

Functions

We are going to create just one function. The purpose of the function is to total the cost of each service provided for a car. This has to take into account the price of the service, the discount amount and the tax amount. Here are a couple of queries that use the function. You can use these to test the function

Select ServicePrice, vs.VehicleServiceID, VehicleID, DiscountPercent,
TaxPercent, dbo.fx_TotalDue(ServicePrice, DiscountPercent, TaxPercent) as Total
From Employee.VehicleServiceDetail vsd
inner Join Employee.VehicleService vs
on vsd.VehicleServiceID=vs.VehicleServiceID
inner join Customer.AutoService a
on a.AutoServiceID=vsd.AutoServiceID

Select SUM( dbo.fx_TotalDue(ServicePrice, DiscountPercent, TaxPercent)) as Total
From Employee.VehicleServiceDetail vsd
inner Join Employee.VehicleService vs
on vsd.VehicleServiceID=vs.VehicleServiceID
inner join Customer.AutoService a
on a.AutoServiceID=vsd.AutoServiceID
Where vsd.VehicleServiceID=4

---

Simple Stored procedures

In a sense this is just redoing the views to add a parameter.

Make a stored procedure that returns the employee information. It will take the parameter @Locationname

Make one that returns all the names, vehicle information and email for registered customers. It will take a parameter of @VehicleLicense