Stored Procedures For Automart Employee

Here is a script for the stored procedures and view we will use in the ASP.Net application

The code for this is at GitHub

Use Automart
Go
--the login will just consist of the employeeID
--and the locationID




Create Proc Employee.usp_GetVehicleandCustomerInfo
@License nvarchar(10)
As
Select LastName, 
Firstname, 
VehicleID,
LicenseNumber, 
vehicleMake, 
VehicleYear
From Person p
inner join Customer.Vehicle v
on p.personkey=v.PersonKey
Where LicenseNumber=@License

Go




Create proc Employee.usp_Services
@VehicleID int
As
Select VehicleServiceID,
LocationName,
ServiceDate,
ServiceTime
From Employee.VehicleService vs
inner join Customer.Location l
on l.locationId=vs.LocationID
Where VehicleID = @VehicleID

go





Create Proc Employee.usp_ServiceDetails
@VehicleServiceID int
As
Select ServiceName, DiscountPercent, TaxPercent, ServiceNotes
From Employee.VehicleServiceDetail vsd
Inner Join Customer.AutoService a
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=@vehicleServiceID

Go

  
Alter Proc Employee.usp_AddNewVehicle
@lastname Nvarchar(255),
@FirstName Nvarchar(255),
@License Nvarchar(10),
@VehicleMake Nvarchar(255),
@vehicleYear Nchar(4)
As
Begin tran
Begin Try
Insert into Person(LastName, firstname)
Values (@lastName, @firstName)

Declare @Personkey int
Select @personkey = max(personKey) from Person

Insert into Customer.Vehicle(LicenseNumber, VehicleMake, VehicleYear, PersonKey)
Values(@License, @VehicleMake, @vehicleYear, @PersonKey)

Commit tran
End try
Begin Catch
Rollback tran
Return error_number()
End Catch

GO


Create proc Employee.usp_AddVehicleService
@vehicleID int,
@LocationID int
As
Declare @ServiceDate Date 
Declare @serviceTime Time
Set @ServiceDate = GetDate()
Set @ServiceTime=GetDate()
Insert into Employee.VehicleService(
VehicleID, 
LocationID, 
ServiceDate, 
ServiceTime
)
Values(
@VehicleID, 
@LOcationID,
@ServiceDate,
@ServiceTime
)
Go

Create proc Employee.usp_AddServiceDetails
@AutoServiceID int,
@DiscountPercent decimal(3,2),
@ServiceNotes xml
As
Declare @TaxPercent decimal(3,2)
Declare @VehicleServiceID int
Set @TaxPercent = .09;
Set @VehicleServiceID=IDENT_CURRENT('Employee.VehicleService')
Insert into Employee.VehicleServiceDetail(
VehicleServiceID, 
AutoServiceID, 
DiscountPercent, 
TaxPercent, 
serviceNotes
)
values(
@VehicleServiceID, 
@AutoServiceID, 
@DiscountPercent, 
@TaxPercent, 
@serviceNotes)
Go
Create view Employee.vw_Services
As
Select ServiceName, AutoServiceID 
From Customer.AutoService
Go
Create login EmployeeLogin with password='P@ssw0rd1', 
default_database=Automart

Go
Create user EmployeeUser for Login EmployeeLogin with default_schema=Employee
Go

Grant exec on schema::Employee to EmployeeUser
Grant select on Schema::Employee to EmployeeUser