Functions, Simple Procedures

--Functions

Use CommunityAssist
Go
Create function fx_Cubed
(@number int)
Returns int
As
Begin
Return @number * @number * @number
End

Select Personkey, dbo.fx_Cubed(PersonKey) as cubed 
From Person

--For every donation 77% goes to charity
--33% goes for maintainance 
Go
Create Function fx_ToCharity
(@donation money)
Returns money
As
Begin
Return @donation *.77
End

go
Create Function fx_Maintainance
(@donation money)
returns money
as
Begin
Return @donation *.33
End
Go

Select donationamount, 
  dbo.fx_toCharity(donationAmount) as Charity,
  dbo.fx_Maintainance(DonationAmount) as Maintainance
From Donation

Select year(DonationDate) as [Year],
sum(dbo.fx_ToCharity(donationamount)) as [Total charity],
 sum(dbo.fx_Maintainance(donationAmount)) as [Total Overhead]
 From Donation
 Group by year(donationDate)
Go

Alter Function fx_MoreAdvancedToCharity
(@donation money, @Percent decimal(6,2))
Returns money
As
Begin
if(@Percent >1.00)
Begin
Set @Percent = @percent/100
End
return @donation * @Percent
End

Select donationAmount, 
dbo.fx_MoreAdvancedToCharity(donationAmount, 75) as [to Charity], 
dbo.fx_MoreAdvancedTocharity(donationAmount, .25) as Overhead
From Donation

Go
Alter procedure usp_ReturnDonorInfo
@Lastname nvarchar(255) 
as
Select lastname, Firstname, Street, City, State, Zip, contactInfo
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join PersonContact pc
on p.PersonKey=pc.PersonKey
Where Lastname=@lastName

Execute dbo.usp_ReturnDonorInfo 
@lastname='Manning'