Functions

Here is a script for creating functions:

/***********************************************
SCALAR FUNCTIONS
**********************************************/

use MagazineSubscription

/***********PARAMETERS AND VARIABLES********************
all parameters and variables in SQL server begin with the @
@var, @leasenumber, @total
Then they are given a data type
@var int
Parameters are values that must be passed to the function or procedure
when it is called
SELECT dbo.func_SubscriptionEndDate(5,'4/4/2007')
Variables are internal to functions and procedures.
They are not provided by the user
Internal variables are declared with the 'DECLARE' keyword
DECLARE @end datetime
They can be assigned values either with "SET" or "SELECT"
SET @end =GetDate()
SELECT @end=startdate from Subscription
*********************************************************/

--here is a simple function that cubes an integer
Create Function func_cube
(@num int) --parameter
returns Int --return type
As --start definition
Begin --begin function body
Declare @cube int --dclare an internal variable
Set @cube = @num *@num *@num --assign it a value
Return (@cube) --return it
End --end function body

--use the function
Select dbo.func_cube(23)

/*Here is a function that takes the subscription type
and the beginning date and returns an end date
for the subscription. The first time you run a function or procedure
you "CREATE" it, Aftwards, When you make changes you "ALTER" it */
Create function func_SubscriptionEndDate
(@type int, @start DateTime) --parameters of the function
Returns Datetime --return type
As --start function definition
Begin --begin body of the function
Declare @end datetime --declare an internal variable
/* the following case structure uses the dateadd function
to add the appropriate units to the original date */
Select @end=
case
When @type =1 then dateAdd(mm,6,@start)
when @type =2 then dateAdd(yy,1,@start)
When @type =3 then dateAdd(mm,3,@start)
when @type =4 then dateadd(yy,2,@start)
when @type =5 then dateAdd(yy,5, @start)
when @type =6 then dateAdd(yy,3,@start)
Else GetDate()
end --end the case
return (@end)
End -- end the function

--use the function with literal values you must
--always specify the schema with a user defined function
--the default is dbo database owner
Select dbo.func_SubscriptionEndDate(5,'4/4/2007')


--use the function in a real query
Select SubscriptionID, CustID, SubscriptionStart,
dbo.func_SubscriptionEndDate(SubscriptTypeID, Subscriptionstart)as EndDate
From Subscription s
Inner Join MagazineDetail md
on s.magdetid=md.magdetid

Here is a function to determine the end date of a subscription

USE [MagazineSubscription]
GO
/****** Object: UserDefinedFunction [dbo].[func_SubscriptionEndDate] Script Date: 02/23/2009 11:35:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[func_SubscriptionEndDate]
(@type int, @start DateTime) --parameters of the function
Returns Datetime --return type
As --start function definition
Begin --begin body of the function
Declare @end datetime --declare an internal variable
/* the following case structure uses the dateadd function
to add the appropriate units to the original date */
Select @end=
case
When @type =1 then dateAdd(mm,6,@start)
when @type =2 then dateAdd(yy,1,@start)
When @type =3 then dateAdd(mm,3,@start)
when @type =4 then dateadd(yy,2,@start)
when @type =5 then dateAdd(yy,5, @start)
when @type =6 then dateAdd(yy,3,@start)
Else GetDate()
end --end the case
return (@end)
End