Home made Functions

 --functions 
 --scalar functions operate on one row at time
 --table valued function
 --aggregate functions 

 use automart
 go

 --here is an a very simple function
 Create function fx_Cube
 (@number int ) --parameters, provided by the user
 returns int --return type for the function note the s
 As
 Begin -- begin function
 Declare @Cube int --declare a variable
 Set @Cube = @number * @number * @number
 return @cube --value to return
 End
 Go

 --using the function
 Select dbo.fx_Cube(37) as Cube

 go
 --altering the function to change the data type
  alter function fx_Cube
 (@number money )
 returns money
 As
 Begin
 Declare @Cube money
 Set @Cube = @number * @number * @number
 return @cube
 End

 Go
 ---use the changed function
 Select ServicePrice, dbo.fx_cube(ServicePrice) as increase
 From Customer.AutoService

Go
--this uses an if statement
Create function fx_GetPercent
(@number decimal(10,2), @percent decimal(3,2))
Returns decimal(10,2)
As
Begin
Declare @percentage decimal(10,2)
Set @Percentage = @number * @Percent
Return @Percentage
End
Go 
Select ServicePrice, dbo.fx_GetPercent(ServicePrice, .05) Discount,
ServicePrice -dbo.fx_GetPercent(ServicePrice, .05) DiscountedPrice
From Customer.AutoService
Go
--added an if statement
Alter function fx_GetPercent
(@number decimal(10,2), @percent decimal(3,2))
Returns decimal(10,2)
As
 Begin --begin function
 Declare @percentage decimal(10,2)
  if @Percent > 1
   Begin --begin if
   Set @Percent=@Percent /100
  End --end if
 Set @Percentage = @number * @Percent
 Return @Percentage
End --end function

--- price is the autoservice table
-- in employee service detail discount percent and tax percent
--0's have 
Go
Alter function fx_Percentages
(@amount decimal(10,2), @percent decimal(3,2))
returns decimal(10,2)
AS
Begin --begin function
 Declare @result decimal(10,2)
  if @amount != 0
   Begin
    Set @result =@Amount-(@amount * @Percent)
   End --end if
  Else
   Begin --begin else
    Set @result=@amount
   End --end else
return @Result
End --end function

Go
Select * From Employee.VehicleServiceDetail
-- Here are a couple of fairly complex queries to figure out
--the totals for a service

Select VehicleServiceID, ServiceName, ServicePrice, DiscountPercent, 
dbo.fx_Percentages(ServicePrice, DiscountPercent) as [Discount Price],
dbo.fx_GetPercent(ServicePrice, TaxPercent) as Tax
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=3

Select VehicleServiceID, 
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent)) as SubTotal,
Sum(dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Tax,
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent) + 
 dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Total
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=3
Group by VehicleServiceID