Functions and Simple Stored Procedures

--funtions

--a function that cubes a number

use communityAssist
go

create function fx_Cubed
(@number int)
Returns int
As
Begin
return @number * @number * @number
End

Select dbo.fx_Cubed(5) as [cubed]
Go
--convert the percentage to decimal
--multiply the amount by the converted percentage
--return the percent amount
Alter function fx_DonationPercent
(@Amount money, @Percentage decimal(5,2))
returns money
as
begin
 if (@percentage > 1)
 Begin
  set @percentage = @percentage / 100
 end
return @amount * @percentage
end

--using the function
Select donationAmount, 
dbo.fx_DonationPercent(donationAmount, 78) as [to charity],
dbo.fx_DonationPercent(donationamount, 22) as [to Organization]
from Donation

--using the function with other functions
Select '$' + cast(sum(DonationAmount) as nvarchar)as total,
'$' + cast(sum(dbo.fx_DonationPercent(donationAmount, 78) ) as nvarchar)as [to charity],'$' + cast(sum(dbo.fx_DonationPercent(donationamount, 22)) as nvarchar)as [to Organization]
from Donation

--using the function with a decimal instead of a whole number
Select donationAmount, 
dbo.fx_DonationPercent(donationAmount, .78) as [to charity],
dbo.fx_DonationPercent(donationamount, .22) as [to Organization]
from Donation

Go
---parameterized views
Alter procedure usp_Donations
--the user provides this paramater value
@PersonID as int
As --bebinig of the body of the stored procedure
Select LastName,
Firstname,
DonationDate,
DonationAmount
From Donation
inner join Person
on Person.PersonKey = donation.PersonKey
where donation.PersonKey = @PersonID 

--calling the stored procedure
execute usp_Donations 3

--alternate way to call the stored procedure
usp_donations
@PersonID =3

Select * from Donation
go
--this is a more complex version of the stored procedure
--it takes lastname, firstname and emails as parameters
--then it uses them to look up the PersonKey
--then it uses the personkey as a criteria for the query
Alter Procedure usp_DonationsByName
@lastname nvarchar(255),
@firstname nvarchar(255),
@email nvarchar(255)
As
--declare a variable to store the personkey
Declare @PersonKey int
--get the value of the personkey
--based on the last and first names
--and email
Select @Personkey=p.personkey 
from Person p
inner join PersonContact pc
on p.PersonKey=pc.Personkey
Where LastName=@LastName
And FirstName=@firstName
And ContactInfo = @Email
And ContactTypeKey=6
--a print statment can be used as a check
--when you are troubleshooting
--you should remove it from the final version
Print 'PersonKey = ' + cast(@PersonKey as Nvarchar)
--run the query with personkey
--as a parameter in the where clause
Select LastName,
Firstname,
DonationDate,
DonationAmount
From Donation
inner join Person
on Person.PersonKey = donation.PersonKey
where donation.PersonKey = @Personkey

--using the procedure
usp_donationsByName
@lastName='Mann',
@FirstName='Louis',
@Email='lmann@mannco.com'