Stored Procedures

use Automart

--stored procedures
--this is a simple stored procedure
--that is basically a parameterized view
go
Create proc usp_GetRegisteredCustomer
@CustomerID int
As
Select LastName
,FirstName
,Email
,LicenseNumber
,VehicleMake
,VehicleYear
From Person p
inner Join Customer.Vehicle v
on p.Personkey=v.PersonKey
inner join Customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
Where p.Personkey=@CustomerId


Go
--the exec keyword is optional but a good idea
--this executes the stored procedure
exec usp_GetRegisteredCustomer @CustomerID=3

Go
--this is more elaborate stored procedure
--alter the procedure if you make changes
--the procedure checks to see if the customer
--already exists. if the customer does exist
--the procedure aborts (the return)
--otherwise it inserts the new customer
--the three insert statments are contained in
--a transaction and a try catch
--if there is no error in any of the inserts
--the transaction commits and the inserts
--are written; if there is an error, the program
--falls to the catch and the inserts are rolled
--back. this prevents fragments from being
--inserted. Either all inserts go through or
--none of them
create proc usp_AddNewCustomer
@LastName nvarchar(255),
@FirstName nvarchar(255),
@LicenseNumber nvarchar(10),
@VehicleMake Nvarchar(255),
@VehicleYear nchar(4),
@Email nvarchar(255),
@Password nvarchar(20)
As
if exists
 (Select lastName, FirstName, email 
 From Person p
 inner join customer.RegisteredCustomer rc
 on p.Personkey=rc.PersonKey
 where lastname=@LastName
 And firstname=@firstName
 And email =@Email)
Begin
Print 'The customer already exists'
Return
End

Begin tran
Begin try
Insert into Person(LastName, firstName)
Values(@LastName, @FirstName)

Declare @PersonKey int
Set @PersonKey=IDENT_CURRENT('Person')

Insert into Customer.Vehicle(LicenseNumber, VehicleMake, VehicleYear, personKey)
Values(@LicenseNumber, @VehicleMake, @VehicleYear, @PersonKey)

insert into Customer.RegisteredCustomer(Email, CustomerPassword, PersonKey)
Values (@Email, @Password, @PersonKey)
Commit tran
end try
Begin Catch
Rollback tran

End catch

--this inserts a new customer the first time
--if you try to run it again with the same
--values it will say the customer exists
--and abort
exec usp_AddNewCustomer
@LastName ='Nelson',
@FirstName ='Lonny',
@LicenseNumber ='375 XCD',
@VehicleMake ='Cadillac',
@VehicleYear='2014',
@Email ='ln@gmail.com',
@Password ='password'

--just checking
Select * From Person
Select * From Customer.Vehicle where Personkey=60
Select * From customer.RegisteredCustomer where PersonKey=60

--a stored procecure to update the license number
--of a vehicle
--if the vehicle exists it will update it
--if not it will insert a new vehicle
Go
Alter proc usp_UpdateVehicle
@LicenseNumberOld nvarchar(10),
@LicenseNumber nvarchar(10),
@VehicleMake nvarchar(255),
@VehicleYear nchar(4),
@PersonKey int
As
if exists
 (Select LicenseNumber from Customer.Vehicle
 Where LicenseNumber = @LicenseNumberOld
 And personkey =@PersonKey)
Begin

Update Customer.Vehicle
Set LicenseNumber=@LicenseNumber
Where PersonKey=@PersonKey
and VehicleMake=@VehicleMake
and VehicleYear = @VehicleYear
End

Else
Begin
Insert into Customer.Vehicle(LicenseNumber, VehicleMake, VehicleYear, PersonKey)
Values(@LicenseNumber, @VehicleMake,@VehicleYear, @PersonKey)
End


Select * From Customer.Vehicle
Go
--update a vehicle 
exec usp_UpdateVehicle
@LicenseNumberOld ='New 123',
@LicenseNumber ='DOC 123',
@VehicleMake ='Toyota Camry',
@VehicleYear ='2004',
@PersonKey =1

--get an existing value out of a column
Declare @key  int
Declare @Email nvarchar(255)
set @Email='candyman@gmail.com'
--you can use a select to get an existing value from a table
--and assign it to a variable
Select @Key = personkey from Customer.RegisteredCustomer
where Email=@Email
Select @Key
 
 Select * from customer.RegisteredCustomer

Community Assist Registration Service

Here is the github location for the code we did in class today https://github.com/spconger/CommuntyAssistRegistrationService

Creating Functions

--creating functions 
--Aggregate, Scalar, TableValued
Use Automart

-- here is a really simple function that takes
--an integer as an argument and returns
--the cube of the integer
go
Create function CubeIt
(@number int) --parameters to pass in
returns int --set return type
As--always start with an as
Begin--begin function block
Declare @cubed int
set @cubed=@number*@number*@number
return @cubed
End--end Function block

Go

Select dbo.cubeiT(4) as cubed
--this function determins if an employe has
--worked more than 5 years
--it takes employeeId and hiredate
--as parameters
Go
Alter function ServiceRecognition
(@employeeID int, @hireDate date)
returns Nvarchar(50)--parameters
As--start function
Begin--begin function block
--declare a variable--all variables must start with @
   Declare @currentDate date
   --set the value of the variable
   Set @currentDate=GETDATE()
   Declare @years int
   Set @years = DateDiff(yy,@HireDate, @CurrentDate)
   Declare @message Nvarchar(50)
   if @years > 5 --if statement to check years
      Begin--begin if block
         set @message= cast(@EmployeeID as Nvarchar(2)) + ',  has been here ' 
          + cast(@years as nvarchar(4)) + ' Give them a raise '
       End --end if block
    else
       Begin --begin else block
          set @message= cast(@EmployeeID as Nvarchar(2))+ ' has been here ' + cast(@years as nvarchar(4))
       End--end else block
   return @message --return result
End
go 

--use the function in a query
--you must specify the function owner, dbo in this case
Select EmployeeID, LastName, FirstName, Hiredate, 
dbo.ServiceRecognition(employeeID, HireDate) as Years
From Person p
inner join Employee e
on p.PersonKey=e.Personkey
Where employeeID=4


--Determine the amount due for a service
--the price is in AutoService
--Other pricing Employee.ServiceDetail
Go
Create function fx_AmountDue
(@ServiceKey int, 
@discount decimal(3,2), 
@Tax decimal(4,3)=.095)--parameters
returns money --return type
As
Begin
   Declare @Price money
   Select @Price=serviceprice from Customer.AutoService
   Where autoServiceID=@serviceKey
   Declare @Total money
   if @discount is null
     Begin
          set @Total=@price + (@price*@Tax)
      End
   Else
      Begin
          set @total = (@price - (@Price * @discount))+@price*@tax
      end
    return @Total
End

--use the function in a query
Select ServiceDate, ServiceName, VehicleID, ServicePrice, TaxPercent, DiscountPercent,
dbo.fx_AmountDue(sd.autoserviceID, discountPercent, TaxPercent) as subTotal
From Customer.AutoService s
inner Join Employee.VehicleServiceDetail sd
on s.AutoServiceID=sd.AutoServiceID
inner join Employee.VehicleService vs
on vs.VehicleServiceID=sd.VehicleServiceID
Where sd.VehicleServiceID =3


--use the function with an aggregate
Select Sum(dbo.fx_AmountDue(sd.autoserviceID, discountPercent, TaxPercent)) as Total
From Customer.AutoService s
inner Join Employee.VehicleServiceDetail sd
on s.AutoServiceID=sd.AutoServiceID
inner join Employee.VehicleService vs
on vs.VehicleServiceID=sd.VehicleServiceID
Where sd.VehicleServiceID =3

Community Service Services and Client Examples

Here is the path to the code for Tuesday 2/10/2015 code on github https://github.com/spconger/CAServiceExample

Here is the code to the code for the client https://github.com/spconger/CommunityAssistClient2015

Inheritance Examples

Here is the link to the code on GitHub https://github.com/spconger/InheritanceExampleJava

Here is a second link to our Wednesday Night example https://github.com/spconger/SecondInheritanceExample115

Indexes and Views

--indexes and joins
--clustered indexes, non clustered indexes, unique, filtered

Use Automart

--the syntax is  CREATE [type of Index] [Index Name] ON [Table](Column Name]
--nonclustered is the default. You never have to actually write it
--a nonclustered index creates a B-tree that breakes the data into
--nodes. The search can locate the relevant node in 2 or three steps
--rather than run through thousands of individual rows

Create nonclustered Index ix_LastName on Person(lastName)
--unique indexes ensure that a column is unique. It speeds up searches
--because the server doesn't have to look for duplicated

Create unique index ix_ServiceName on Customer.AutoService(ServiceName)
--a filtered index has a where clause that can "filter" which rows
--in a table are indexed

Create nonclustered index Ix_olderData on Employee.VehicleService (ServiceDate) Where serviceDate > '1/1/2015'

--it is possible to include more than one column in an index
Create nonclustered index ix_employeeLoc on Employee(PersonKey, LocationID)

--primary keys are indexed by default
--but here is the syntax fro how to create one
Create clustered index ix_PersonKey on Person(PersonKey)

--forcing an index. SQL Server will not even create the index structure
--the b-tree for tables under a certain number of rows (27000 or so)
--the following syntax forces the use of the ix_Lastname index
Select Lastname, firstName, email
From Person p with (index(ix_lastName)) --this forces the index
inner join Customer.RegisteredCustomer rc
on rc.PersonKey=p.Personkey
where LastName='Smith'

--Go is used to separate batches. It means basically
--finish everything before starting the next command
Go
--Views--the basic syntax is CREATE VIEW [Name] AS then 
--SQL Statement. Views are basically stored queries
--they are filters. They don't store the actual data.
--The idea of a view is to create a "View" of the database
--for a particular set of users. Human Resources, for instance.
--Some views can be used for updates and inserts but not
--most. To allow updating and inserting the view must
--be transparent. No aliases, not more than one join,
--no calcualted fields. It is probably better to use stored
--procedures rather than views for those tasks
Create View vw_RegisteredCustomers
AS
Select LastName [Last Name]
,FirstName [First Name]
, Email
,LicenseNumber License
,VehicleMake Make
,VehicleYear [Year]
From Person p
inner join Customer.RegisteredCustomer rc
on p.Personkey=rc.PersonKey
inner Join Customer.Vehicle v
on v.PersonKey=p.Personkey

Go
--the order by clause is forbidden in creating views
--but you can order the results of a query using
--a view. Also when Selecting from a view
--you must use the aliases as the column names
Select * From vw_RegisteredCustomers
Where [Last name] = 'Smith'
order by [First Name]

Sub query example and Create and Alter Tables

Here is the subquery example

--get total number of grants, count denied, count reduced, percents

Select count(GrantKey) [Total Grants],
(Select count(GrantKey) From ServiceGrant where GrantApprovalStatus='denied') [Total Denied],
(Select count(GrantKey) From ServiceGrant where GrantApprovalStatus='reduced')[Total Reduced],
cast(
Cast((Select count(GrantKey) From ServiceGrant where GrantApprovalStatus='denied')as decimal(5,2))
/cast
(count(GrantKey)as Decimal(5,2)) * 100 as Decimal(5,2)) as [Percent Denied],
cast(
Cast((Select count(GrantKey) From ServiceGrant where GrantApprovalStatus='reduced')as decimal(5,2))
/cast
(count(GrantKey)as Decimal(5,2)) * 100 as Decimal(5,2)) as [Percent Reduced]

From ServiceGrant

---

Here are the Table and alter table examples with comments

Use Master
--create a new database according to the model template
Create Database BookReview
--make sure you change the context to the new
--database
Use BookReview

--create a table
Create table Book
(
--identity creates an autonumber, in this case
--starting at 1 and incrementing by 1s
--the primary key is declared inline which
--means SQL Server names it
 BookKey int identity(1,1) primary Key,
 BookTitle NVarchar(255) not null,
 BookISBN NChar(13) null,
 BookPublishYear Int 
)

Create Table Author
(
 AuthorKey int Identity(1,1) not null,
 --declaring the key in a constraint
 --allows you to name it
 Constraint PK_Author Primary Key(AuthorKey),
 AuthorName Nvarchar(255) not null,
 AuthorDates Nvarchar(255)
)

Create Table AuthorBook
(
 BookKey int not null,
 AuthorKey int not null,
 --a composite primary key
 Constraint PK_AuthorBook Primary Key(bookKey, AuthorKey),
 --two foreign keys
 --the foriegn key takes a field in this table and relates it
 --"references" the field in another table
 Constraint FK_Book Foreign Key(BookKey) references Book(BookKey),
 
 Constraint FK_Author Foreign Key(AuthorKey) references Author(AuthorKey)
 
)

Create Table Review
(
 ReviewKey int identity(1,1) not null,
 BookKey int not null,
 --a default constraint
 ReviewDate Date default GetDate(),
 ReviewerKey int not null,
 ReviewRating int not null,
 --a check constraint lets you set a set or range of 
 --acceptable values for a field
 Constraint chk_Rating Check (ReviewRating between 0 and 5),
 --NVarchar(max) lets you store up to 2 gigs of text, but
 --the text is not stored in the table itself, only a pointer to
 --the text is stored in the table. You can retrieve the contents,
 --but you cannot query the contents of the field or use it in a 
 --where clause
 ReviewText Nvarchar(max) not null
 

)

Create table Reviewer
(
 ReviewerKey int identity(1,1) not null,
 Reviewername nvarchar(255) not null,
 --the unique constraint forces the email
 --to be unique, to never repeat
 ReviewerEmail nvarchar(255) not null unique

)

--to change an existing table you either need to drop and 
--recreate it or alter it

--adds a primary key after the table has been made
Alter table Review
Add constraint PK_Review Primary Key(ReviewKey)

--add a foreign key
Alter Table Review
Add Constraint fk_BookRev Foreign Key(bookKey) References Book(BookKey)

Alter table Reviewer
Add constraint PK_Reviewer Primary Key(ReviewerKey)

Alter Table Review
Add Constraint fk_Reviewer Foreign Key(ReviewerKey) References Reviewer(ReviewerKey)

--add a column
Alter table Reviewer
Add ReviewerPhone nvarchar(13) not null

--Drop a column
Alter Table Reviewer
Drop Column ReviwerPhone

--drop the table itself
Drop Table Review

A fix for Assignment 3

There is a permissions error in the Fan log in. To fix it Go to TOOLS in the Visual Studio menu, choose SQL, NEW QUERY. Connect the database to .\sqlexpress. Type in the following SQL.

Use ShowTracker
Grant Select on FanLogin to FanRole

Once you run this code you should have the permissions you need

---

There are a couple of other common problems.

In the Login class there is a line (47) that assigns the userkey to key

  key = u.ReviewerKey;

For some reason with the showtracker database we have to cast this to an int

  key = (int)u.ReviewerKey;

Finally, be aware the random seed that is concatenated with the password is "LoginRandom" in the fanlogin table, not "FanKey" or "FanLoginKey."