Functions temp tables

use Community_Assist

--temporary tables 
--local temporary table. Local to this one session
Create Table #TempGrants
(
 GrantRequestKey int,
 GrantRequestDate datetime,
 PersonKey int,
 GrantTypeKey int,
 GrantRequestAmount money
)

insert into #tempGrants(GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount)
Select GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount from GrantRequest
Where Month(GrantRequestDate)=9

Select * from #TempGrants

--Global temporary table
Create Table ##TempGrantsGlobal
(
 GrantRequestKey int,
 GrantRequestDate datetime,
 PersonKey int,
 GrantTypeKey int,
 GrantRequestAmount money
)

insert into ##tempGrantsGlobal(GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount)
Select GrantRequestKey,
GrantRequestDate,PersonKey, GrantTypeKey,
GrantRequestAmount from GrantRequest
Where Month(GrantRequestDate)=9

Select * from ##TempGrantsGlobal
--Functions
Go
Create Function fx_Cube
(@number int)
returns int
As
Begin
Declare @Cube int
Set @cube = @number * @number * @number
return @Cube
End
Go
Select dbo.fx_Cube(3) as [Cube]

--stored procedures
--triggers
 Select PersonKey,dbo.fx_cube(PersonKey) as cubed
 from Person
 order by cubed

 go
 Alter function fx_PercentAmount
 (@amount money, @percentage decimal(10,2)) -- parameters
 returns money --return type
 As
 Begin
 if(not @amount=0) --check to make sure not 0
 Begin --start outer if
 
     if(@Percentage > 1) --check to see how % entered
     Begin --begin inner if
   Set @Percentage = @percentage / 100
     End --end inner if
   Declare @result money --declare variable for result
   Set @result = @amount * @percentage --calculate amount
 End --end outer if
 Else --begin else
 Begin 
 Set @Result=0;
 End --end else
  return @result --return results
 End

 Select DonationAmount, dbo.fx_PercentAmount(DonationAmount, 20) as Org, 
 dbo.fx_PercentAmount(donationAmount, .8) as charity
 From Donation

  Select Sum(DonationAmount) Total, dbo.fx_PercentAmount(sum(DonationAmount), 20) as Org, 
 dbo.fx_PercentAmount(Sum(donationAmount), .8) as charity
 From Donation