Functions, Stored Procedures

--functions 

use CommunityAssist
-- some system views

select * from sys.indexes

select * from sys.tables

--returns info on the table donation
sp_Help 'Donation'


Go
--create a simple function

Create function fn_Cube
(@number int) --user provided parameter
returns int --return type
As 
Begin --begin function body
--must return an integers
return @number * @number * @number
End --end of function body

--use the function. It must be used with the schema
--owner, in this case dbo (data base owner)
Select Dependents, dbo.fn_Cube(Dependents) cubed
From Employee where Dependents is not null

Go
--for this function we will use an arbitrary rule
--every thing up to a 1000 100% deductable
--after 1000 80%
create function fn_TaxDeduction
(@PersonKey int)
returns money
as
Begin 
declare @total money --declare an internal variable
declare @deductible money
--assign a value to the variable with
--a select statement
Select @total = SUM(DonationAmount) 
 From Donation
 Where PersonKey=@personKey
--test the value with an if statement
if @total >1000
Begin --start of if true
 set @deductible=@total * .8
End --end of if block 
Else
Begin --else block
 set @deductible=@Total
End --else block
 Return @Deductible --return the results
End

--use the function
Select personkey, sum(donationAmount) as total, 
dbo.fn_TaxDeduction(personKey) Deductible
From donation
Where dbo.fn_TaxDeduction(personKey) is not null
Group by personkey, dbo.fn_TaxDeduction(personKey)
order by Deductible desc

Go
--this is a mess but shows a parameterized view
--it does point out some of the dangers of 
--inner joins
alter proc usp_DonorInfo
@personKey int
As
Select Distinct lastName, Firstname,
Street, City, [State], ContactInfo, Donationkey,DonationAmount
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join PersonContact pc
on p.personkey=pc.PersonKey
inner join donation d
on p.Personkey=d.personkey
where p.personkey=@PersonKey


--execute the stored procedure
--you must provide a value for the parameter
exec usp_donorInfo 3

Select * From donation
--another stored procedure
--a parameterized view
Create proc usp_DonationSummary
@Year int
As
Select YEAR(donationDate) [Year],
MONTH(donationDate) [Month],
SUM(donationAmount) total
From Donation
Where Year(donationDate)=@Year
Group by Year(donationDate), 
Month(DonationDate)

Select distinct YEAR(DonationDate) from Donation

--another way to assign the parameter
exec usp_DonationSummary 
@Year=2010