Functions and Temporary Tables

use Community_Assist
--temporary tables
Create table #TempTable
(
   PersonKey int,
   personLastName nvarchar(255),
   PersonFirstName nvarchar(255),
   PersonEmail nvarchar(255)
)

Insert into #tempTable (PersonKey, personLastName,PersonFirstName, PersonEmail)
Select Personkey, PersonLastName, PersonFirstName, PersonEmail
From Person 

Select * from #TempTable

Create table ##TempTable2
(
   PersonKey int,
   personLastName nvarchar(255),
   PersonFirstName nvarchar(255),
   PersonEmail nvarchar(255)
)
Insert into ##tempTable2 (PersonKey, personLastName,PersonFirstName, PersonEmail)
Select Personkey, PersonLastName, PersonFirstName, PersonEmail
From Person 

--functions--scalar
go
Create function fx_Cube
(@number int)
returns int
As
Begin
Declare @cube int
Set @Cube = @number * @number * @number
return @Cube
End
Go

Select EmployeeKey, dbo.fx_Cube(EmployeeKey) as cubed from Employee

Select * from Person
go
/* this one doesn't work for some reason
Alter Function fx_Address
(@Address nvarchar(255),
@apartment nvarchar(255),
@City nvarchar(255),
@State nvarchar(255),
@Zip nvarchar(255))
returns nvarchar(255)
As
Begin
Declare @complete nvarchar(255)

if @Apartment is not null
   Begin
   set @complete = @address + ' ' + @Apartment + ' '
   + @city + ', ' + @state + ' ' + @zip
   End
Else 
   Begin
   set @complete = @address + ' ' + @city + ', ' + @state + ' ' + @zip
   End
return @Complete
End */

go
go
Alter function fx_OneLineAddress
(@Apartment nvarchar(255),
@Street  nvarchar(255),
@City  nvarchar(255),
@State nchar(2),
@Zip nchar(9))
returns nvarchar(255)
as
Begin
Declare @address nvarchar(255)
if @Apartment is null
   Begin
     Set @Address=@Street + ', ' + @City + ', ' + @state + ' ' + @zip
   End
else
   Begin
      Set @Address= @Street + ', ' + @Apartment + ', ' + @City + ', ' + @state + ' ' + @zip
   End
   return @Address
End
go

Select PersonLastName, PersonFirstName, 
dbo.fx_oneLineAddress(
PersonAddressApt, 
PersonAddressStreet, 
PersonAddressCity, 
PersonAddressState, 
PersonAddressZip) as [Address]
From Person p
inner Join PersonAddress pa
on p.PersonKey = pa.PersonKey
go

Create function fx_RequestMax
(@GrantTypeKey int,
@RequestAmount money)
returns money
As
Begin
Declare @Max money
Select @Max=GrantTypeMaximum from GrantType
Where GrantTypeKey = @GrantTypeKey
Declare @Differance money
set @Differance = @max - @RequestAmount
Return @Differance
End
go
Select GrantRequestKey, GrantRequestDate, GrantRequestAmount,
dbo.fx_RequestMax(GrantTypeKey, GrantRequestAmount) as Diff
From GrantRequest