Functions and stored procedures 1

Use magazineSubscription

--functionname(functionArguments)
Go
Create Function func_Cube
(@number int)
Returns int
As
Begin
Declare @cube int
Set @cube=@number * @number * @number
Return @cube
End

Select SubscriptTypeID, dbo.Func_Cube(SubscriptTypeID)
From SubscriptionType

Go
Create Function func_SubscriptionEndDate
(@Type int, @startDate DateTime)
Returns DateTime
As
Begin
Declare @endDate DateTime
Select @endDate =
Case @Type
When 1 then DateAdd(mm,6,@startdate)
when 2 then DateAdd(yy, 1, @startDate)
when 3 then DateAdd(mm, 3, @startDate)
when 4 then DateAdd(yy, 2, @startDate)
when 5 then DateAdd(yy, 5, @startDate)
when 6 then DateAdd(yy, 3, @startDate)
Else GetDate()
End
Return @EndDate
End

Select dbo.func_SubscriptionEndDate(1, '5/9/2011')

Select * from MagazineDetail
Select * from SubscriptionType
Insert into Subscription(
CustID,
MagDetID,
SubscriptionStart,
SubscriptionEnd)
Values(1, 16, '5/9/2011',
dbo.func_SubscriptionEndDate(6,'5/9/2011'))

Select * from Subscription
Go
Create proc usp_CustomerCity
@City varchar(50)
AS
Select CustLastName as [Last Name],
CustFirstName as [First Name],
CustAddress as [Address],
CustCity as City,
CustState as [State],
CustZipcode as [Zip code],
CustPhone as [Phone]
From Customer
Where CustCity=@City

usp_CustomerCity 'Seattle'

exec usp_CustomerCity
@City='Seattle'
Go
Create proc usp_CustomerCityZip
@City varchar(50),
@Zip char(11)
AS
Select CustLastName as [Last Name],
CustFirstName as [First Name],
CustAddress as [Address],
CustCity as City,
CustState as [State],
CustZipcode as [Zip code],
CustPhone as [Phone]
From Customer
Where CustCity=@City
And CustZipcode=@Zip

usp_CustomerCityZip
@City='Seattle',
@Zip='98190'