Creating Functions

--creating functions 
--Aggregate, Scalar, TableValued
Use Automart

-- here is a really simple function that takes
--an integer as an argument and returns
--the cube of the integer
go
Create function CubeIt
(@number int) --parameters to pass in
returns int --set return type
As--always start with an as
Begin--begin function block
Declare @cubed int
set @cubed=@number*@number*@number
return @cubed
End--end Function block

Go

Select dbo.cubeiT(4) as cubed
--this function determins if an employe has
--worked more than 5 years
--it takes employeeId and hiredate
--as parameters
Go
Alter function ServiceRecognition
(@employeeID int, @hireDate date)
returns Nvarchar(50)--parameters
As--start function
Begin--begin function block
--declare a variable--all variables must start with @
   Declare @currentDate date
   --set the value of the variable
   Set @currentDate=GETDATE()
   Declare @years int
   Set @years = DateDiff(yy,@HireDate, @CurrentDate)
   Declare @message Nvarchar(50)
   if @years > 5 --if statement to check years
      Begin--begin if block
         set @message= cast(@EmployeeID as Nvarchar(2)) + ',  has been here ' 
          + cast(@years as nvarchar(4)) + ' Give them a raise '
       End --end if block
    else
       Begin --begin else block
          set @message= cast(@EmployeeID as Nvarchar(2))+ ' has been here ' + cast(@years as nvarchar(4))
       End--end else block
   return @message --return result
End
go 

--use the function in a query
--you must specify the function owner, dbo in this case
Select EmployeeID, LastName, FirstName, Hiredate, 
dbo.ServiceRecognition(employeeID, HireDate) as Years
From Person p
inner join Employee e
on p.PersonKey=e.Personkey
Where employeeID=4


--Determine the amount due for a service
--the price is in AutoService
--Other pricing Employee.ServiceDetail
Go
Create function fx_AmountDue
(@ServiceKey int, 
@discount decimal(3,2), 
@Tax decimal(4,3)=.095)--parameters
returns money --return type
As
Begin
   Declare @Price money
   Select @Price=serviceprice from Customer.AutoService
   Where autoServiceID=@serviceKey
   Declare @Total money
   if @discount is null
     Begin
          set @Total=@price + (@price*@Tax)
      End
   Else
      Begin
          set @total = (@price - (@Price * @discount))+@price*@tax
      end
    return @Total
End

--use the function in a query
Select ServiceDate, ServiceName, VehicleID, ServicePrice, TaxPercent, DiscountPercent,
dbo.fx_AmountDue(sd.autoserviceID, discountPercent, TaxPercent) as subTotal
From Customer.AutoService s
inner Join Employee.VehicleServiceDetail sd
on s.AutoServiceID=sd.AutoServiceID
inner join Employee.VehicleService vs
on vs.VehicleServiceID=sd.VehicleServiceID
Where sd.VehicleServiceID =3


--use the function with an aggregate
Select Sum(dbo.fx_AmountDue(sd.autoserviceID, discountPercent, TaxPercent)) as Total
From Customer.AutoService s
inner Join Employee.VehicleServiceDetail sd
on s.AutoServiceID=sd.AutoServiceID
inner join Employee.VehicleService vs
on vs.VehicleServiceID=sd.VehicleServiceID
Where sd.VehicleServiceID =3