Stored procedures for CommunityAssist

Here is the SQL we did in class. The C# code is posted on gitHub

use CommunityAssist
go
Create schema Client
Go

Create view client.vw_services
As
Select ServiceKey, ServiceName, ServiceDescription
From [Service]
go
Select * from vw_Services
go

Create Proc Client.uspLogin
@userName nvarchar(255),
@userPassword nvarchar(255)
As
Select PersonKey from Person
Where userName=@userName
And userPassword=@userPassword

usplogin 'JAnderson', 'APass'
Select * From Person
Go

Alter proc Client.usp_Register
@lastName Nvarchar(255),
@FirstName Nvarchar(255),
@userName Nvarchar(255),
@password Nvarchar(255),
@email Nvarchar(255),
@HomePhone Nvarchar(255)
As
Begin Tran
Begin Try
Insert into Person(Lastname, FirstName, UserName, userPassword)
Values(@LastName, @FirstName, @Username, @password)

Declare @Personkey int
Set @PersonKey=ident_current('Person');
--Select @personkey=personkey 
--from Person 
--where PersonKey=(Select max(personkey) from Person)

Insert into PersonContact(ContactInfo, PersonKey, contactTypeKey)
Values (@Email, @PersonKey, 6)

Insert into PersonContact(ContactInfo, PersonKey, contactTypeKey)
Values (@HomePhone, @PersonKey,1)

Commit tran
End Try
Begin Catch
Rollback Tran
return error_number()
End Catch
Go

Client.usp_Register
@Lastname='Danielle',
@FirstName='Mary',
@UserName='maryD',
@password='marypass',
@email='md@gmail.com',
@HomePhone='2065553256'

Select * from PersonContact
Go
Alter proc Client.uspGetGrants
@PersonKey int
As
Select GrantDate, GrantAmount, Servicename, GrantDateApproved
From ServiceGrant sg
inner join [Service] s
on s.ServiceKey=sg.ServiceKey
Where personkey=@personkey
Go

Client.uspGetGrants 1
Go
Create proc Client.uspNewGrant
@GrantAmount money,
@ServiceKey int,
@PersonKey int
As
Insert into ServiceGrant(GrantAmount, GrantDate, PersonKey, ServiceKey)
Values(@GrantAmount, getDate(), @PersonKey, @ServiceKey)

Client.uspNewGrant
300.00,3,52

Select * From ServiceGrant


Create Login ClientLogin with password='P@ssw0rd1', default_database=CommunityAssist
Create user ClientUser for login ClientLogin with default_schema=Client
Grant execute, select on Schema::Client to ClientUser