Views, Procedures

Here is the code, such as it is from the morning class


Select * from Customer.RegisteredCustomer
Select * from Person


--views, parameterized views
--Service history for a particular vehicle by license plate
--Customer name and vehicles by license plate
--service costs and descriptions

--things that need to be done stored procedures
--record the services performed --stored procedure
--get the amount due--function
--maintenance recommendations--stored procedure
--Edit to correct mis-entry or change customer information--stored procedure
--
--
--we did this both as a view and as a stored procedure. The difference is the view
--does not have the parameter @license nor the where clause at the end
Go
Create Proc Employee.usp_VehicleHistory
@License nvarchar(10)
As
Select LastName as [Last Name],
LicenseNumber as [License],
VehicleMake as [Make],
VehicleYear as [Year],
ServiceDate as [Service Date],
ServiceName as [Service],
serviceNotes as [Notes]
From Person p
Inner Join Customer.Vehicle v
on p.PersonKey = v.PersonKey
Inner Join Employee.VehicleService vs
on v.VehicleID=vs.VehicleID
Inner Join Employee.VehicleServiceDetail vd
on vs.VehicleServiceId=vd.VehicleServiceID
Inner Join Customer.AutoService a
on a.AutoServiceID=vd.AutoServiceID
Where LicenseNumber=@License


Select * from Customer.AutoService

Select * from Employee.VehicleHistory
Where License='NET200'

--this view we made with the graphical designer
Select * from dbo.vw_HumanResources
Where LocationName='Federal Way'
Order by LastName


Execute Employee.usp_VehicleHistory 'ITC226'

--vehicleID get by license plate
--LocationID
--ServiceDate Service time

Alter procedure Employee.usp_NewService
@License nvarchar(10),
@LocationID int
As
Begin try
Declare @serviceDate Date
Declare @ServiceTime Time
Set @serviceDate=GETDATE()
Set @ServiceTime=GETDATE()

Declare @VehicleID int
Select @VehicleID=VehicleID From Customer.vehicle
Where LicenseNumber=@License


Insert into Employee.VehicleService
(VehicleID,LocationID,ServiceDate,ServiceTime)
Values (@VehicleID, @LocationID, @serviceDate, @ServiceTime)
End Try
Begin Catch
Print error_message()
End Catch

Exec Employee.usp_NewService
@License= 'ITC226',
@LocationID=2