Functions and Simple Procedures

--a simple function
Create Function fx_cube
(@number int) --parameter provided by the user
returns int --what data type the function returns
As -- function is an alias for the content below
Begin
--result to return
return @number * @number * @number 
End

--using the function (always needs the schema "dbo" 
--or whatever schema owns it
Select dbo.fx_Cube(3)

--using the function with a field
Select EmployeeKey, dbo.fx_cube(EmployeeKey) From Employee

/***********************
always plot out the function or procedure first
the function will return the price of a subscription
parameters magid and subscriptionTypeID
use MagazineId and SubscriptionTypeID to look up price
********************************/
Use MagazineSubscription 
Go

Create function fx_GetSubscriptionPrice
(@magid int, @subscriptTypeID int) --parameters
Returns money --return type
AS
Begin
Declare @Price money --internal variable
--assign the value from a select statement
Select @Price=SubscriptionPrice
From MagazineDetail
Where MagID=@MagID
AND SubscriptTypeID=@subscriptTypeID
Return @Price --return the value

End


Select * From Magazine
Select * From magazineDetail where MagID=2

--using the function
Select MagName, SubscriptTypeName, 
dbo.fx_GetSubscriptionPrice(m.MagID, st.SubscriptTypeID) 
From Magazine m
Inner Join MagazineDetail md
on m.MagID=md.MagID
inner join SubscriptionType st
on st.SubscriptTypeID=md.SubscriptTypeID
Where m.MagID=2

--another way to declare and set a value
--for a variable
Declare @Today DateTime
Set @Today=GETDATE()
Select @Today


Declare @ID int 
set @ID=@@Identity --retursn the last identity
--created in the database


Declare @ID2 int
--returns the last identity created in the table
set @ID2=IDENT_CURRENT('MagazineDetail')
Select @ID2

Select * from MagazineDetail

Use CommunityAssist

--simple stored procedure
--really a parameterized view
Create proc usp_DonorContact
@contactType int --parameter
As
Select Distinct lastname [Last Name],
Firstname [First Name],
ContactInfo [Contact]
From Person p
inner join PersonContact pc
on p.PersonKey=pc.PersonKey
inner join Donation d
on p.PersonKey=d.PersonKey
Where ContactTypeKey=@ContactType

--calling the procedure
--the exec is optional
--6 is the contact type
Exec usp_DonorContact 6