/***********************************************
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


Use aptmanagement

/* this function creates the leaseid given two 
parameters the startdate and the apartment number. */
create function func_LeaseID 
(@startDate Datetime, @apt char(3)) --function parameters
Returns char(10) --return type
WITH EXECUTE AS CALLER --executes for the caller in a select
As -- begin function definition
Begin --start function body
Declare @leaseNumber Char(10) --declare an internal variable
Select @Leasenumber=rtrim(cast(month(@startdate)as char))
	+ rtrim(cast(day(@startdate)as char))
	+ substring(cast(year(@startdate)as char),3,2)
	+ @apt --us the functions we have learned to get the pieces
--of the lease number
return (@leasenumber) -- return the results
End --end the function body

-- call the function. In sql server you must always 
--procede the funtion name with the schema name
--when you use it. the default schema is dbo
select dbo.func_leaseID('4/3/2007','201') as "LeaseNumber"