Scalar Functions One

Here are a couple of examples of scalar functions. Scalar functions only effect one row at time.
All SQL parameters and variables begin with the @ symbol
Parameters are provided by the user (they are the arguments of the function)
Variables are internal to the function and must be declared with the DECLARE key word.
The basic structure of a scalar function is

CREATE FUNCTION <functionName>
(<Parameter1 datatype>, <parameter2 datatype>, . . .
AS
BEGIN
<Function body>
END

Use MagazineSubscription
go

--Basic functions

Create function func_Cube
(@num int)--parameter provided by user
returns int
As
Begin
Declare @cube int --declare variable
Set @cube=@num * @num *@num --assign value
return @cube
End
go
Alter Function func_Cube
(@num decimal(10,2))
returns Decimal(10,2)
As
Begin
Declare @cube decimal(10,2) --declare variable
Set @cube=@num * @num *@num --assign value
return @cube
End
Go

Select SubscriptionPrice, dbo.func_Cube(SubscriptionPrice) as cubed
from MagazineDetail
Go
Select * from SubscriptionType
--get the type
--determine the length in years or months
--calculate the end date
Go
Create Function func_SubscriptionEndDate
(@type int, @startdate datetime)
Returns DateTime
As
Begin
Declare @endDate Datetime
Select @endDate=
Case
When @type=1 then dateadd(mm,6,@startdate)
when @type=2 then dateadd(yy,1,@startDate)
when @type=3 then dateadd(mm, 3, @startDate)
when @type=4 then dateadd(yy, 2, @startDate)
when @type=5 then dateadd(yy,5, @startdate)
when @type=6 then dateadd(yy,3,@startdate)
Else GetDate()
end
Return @endDate

End
Go
Select * from magazineDetail
Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values(1, 3, GETDATE(),dbo.func_subscriptionEndDate(5,GETDATE()))

Select * from Subscription