QVBj2i4w

1Drop Table JobService
2Drop Table Service
3Drop Table JobSupply
4Drop Table Supply
5Drop Table SupplyCategory
6Drop Table Job
7drop table Payment
8Drop Table Client
9Drop Table StaffTraining
10Drop Table Training
11Drop Table Staff
12Drop Table StaffType
13
14Create Table StaffType
15(
16StaffTypeCode int identity (1,1) not null
17constraint pk_StaffType primary key clustered,
18Description varchar (100) not null,
19Wage smallmoney not null
20constraint ck_Wage check (Wage >=0)
21constraint df_Wage default 20,
22)
23
24Create Table Staff
25(
26StaffID int not null
27constraint pk_Staff primary key clustered,
28FirstName varchar (50) not null,
29LastName varchar (50) not null,
30TrainingCredits smallint not null,
31StaffTypeCode int not null
32constraint fk_StaffToStaffType references StaffType(StaffTypeCode),
33Phone varchar(14) not null
34constraint ck_StaffPhone check (Phone like '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
35
36)
37
38Create Table Training
39(
40TrainingID varchar(20) not null
41constraint pk_Training primary key clustered,
42Description varchar(100) not null,
43Credits tinyint not null
44constraint ck_Credits check (Credits <=6)
45constraint df_Credits default 3
46)
47
48Create Table StaffTraining
49(
50StaffID int not null
51constraint fk_StaffTrainingToStaff references Staff(StaffID),
52TrainingID varchar(20) not null
53constraint fk_StaffTrainingToTraining references Training(TrainingID),
54CompletionDate datetime not null,
55constraint pk_StaffTraining primary key clustered (StaffID, TrainingID)
56)
57Create Table Client
58(
59ClientID int identity (1,1) not null
60constraint pk_Client primary key clustered,
61FirstName varchar(50) not null,
62LastName varchar(50) not null,
63Phone char (14) not null
64constraint ck_Phone check (Phone like '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
65Balance money not null constraint df_Balance default  0
66)
67
68Create Table Job
69(
70JobNumber int identity (1,1) not null
71constraint pk_Job primary key clustered,
72Date datetime not null,
73Address varchar(100) not null,
74City varchar (50) not null,
75Province char(2) not null
76constraint ck_Province check (Province like '[a-z][a-z]'),
77PostalCode char(7) not null
78constraint ck_PostalCode check (PostalCode like '[a-z][0-9][a-z] [0-9][a-z][0-9]'),
79SubTotal money not null,
80GST money not null,
81Total money not null,
82StaffID int not null
83constraint fk_JobToStaff references Staff(StaffID),
84ClientID int not null
85constraint fk_JobToClient references Client(ClientID),
86constraint ck_SubTotalandTotal check (Total > Subtotal)
87)
88
89Create Table SupplyCategory
90(
91SupplyCategoryCode varchar (5) not null
92constraint pk_SupplyCategory primary key clustered,
93Description varchar(100) not null,
94StorageRoom varchar (5) not null
95)
96
97Create Table Supply
98(
99SupplyCode varchar(8) not null
100constraint pk_Supply primary key clustered,
101Description varchar(100) not null,
102SupplyCategoryCode varchar(5) not null
103constraint fk_SupplyToSupplyCategory references SupplyCategory(SupplyCategoryCode)
104)
105
106Create Table JobSupply
107(
108JobNumber int not null
109constraint fk_JobSupplyToJob references Job(JobNumber),
110SupplyCode varchar(8) not null
111constraint fk_JobSupplyToSupply references Supply(SupplyCode),
112Quantity smallint not null,
113constraint pk_JobSupply primary key clustered (JobNumber, SupplyCode)
114)
115
116Create Table Service
117(
118ServiceCode varchar(15) not null
119constraint pk_Service primary key clustered,
120Description varchar(100) not null,
121CostPerHour smallmoney not null
122)
123
124Create Table JobService 
125(
126JobNumber int not null
127constraint fk_JobServiceToJob references Job(JobNumber),
128ServiceCode varchar(15) not null
129constraint fk_JobServiceToService references Service(ServiceCode),
130Notes varchar(200) not null,
131hours int not null,
132ActualCostPerHour smallmoney not null,
133ExtCost smallmoney not null,
134constraint pk_JobService primary key clustered (JobNumber, ServiceCode)
135)
136
137Create Table Payment
138(
139PaymentID int not null identity (1,1) constraint pk_Payment primary key clustered,
140Date datetime not null,
141Amount smallmoney not null,
142ClientID int not null
143constraint fk_PaymentToClient references Client(ClientID)
144)
145
146Alter Table Client
147add
148Email varchar(100) null
149constraint ck_Email check (Email like '%@%.%')
150
151Alter Table Staff
152add
153Available char(1) not null
154constraint ck_Available check (Available like '[YN]')
155constraint df_Available default 'Y'
156
157Alter Table Job
158add
159constraint df_Province default 'AB' for Province
160
161--Non clustered indexes here
162
163Create nonclustered index IX_StaffTraining_TrainingID
164on StaffTraining (TrainingID)
165Create nonclustered index IX_StaffTraining_StaffID
166on StaffTraining (StaffID)
167Create nonclustered index IX_Staff_StaffTypeCode
168on Staff (StaffTypeCode)
169Create nonclustered index IX_Job_ClientID
170on Job (ClientID)
171Create nonclustered index IX_Job_StaffID
172on Job (StaffID)
173Create nonclustered index IX_JobService_JobNumber
174on JobService (JobNumber)
175Create nonclustered index IX_JobService_ServiceCode
176on JobService (ServiceCode)
177Create nonclustered index IX_JobSupply_JobNumber
178on Jobsupply (JobNumber)
179Create nonclustered index IX_JobSupply_SupplyCode
180on JobSupply (SupplyCode)
181
182--Lab 2 insert script
183--IMPORTANT! If you need to run this script more than once you must drop and recreate your tables first to reset the identity properties.
184Delete JobService
185Delete Service
186Delete JobSupply
187Delete Supply
188Delete SupplyCategory
189Delete Job
190Delete Client
191Delete StaffTraining
192Delete Training
193Delete Staff
194Delete StaffType
195
196
197
198
199Insert into StaffType (Description,Wage)
200values ('Painter',25)
201Insert into StaffType (Description,Wage)
202values ('Sander',20)
203Insert into StaffType (Description,Wage)
204values ('Builder',30)
205Insert into StaffType (Description,Wage)
206values ('Demolition',35)
207Insert into StaffType (Description,Wage)
208values ('Cleaning',15)
209
210
211Insert into Staff (StaffID,FirstName,LastName,TrainingCredits,StaffTypeCode,phone)
212values (11111,'Jason','Painter',12,1,'(780) 111-2222')
213Insert into Staff (StaffID,FirstName,LastName,TrainingCredits,StaffTypeCode,phone)
214values (22222,'Suzy','Cleaner',12,5,'(780) 111-3333')
215Insert into Staff (StaffID,FirstName,LastName,TrainingCredits,StaffTypeCode,phone)
216values (33333,'Alex','Boom',13,4,'(780) 111-4444')
217Insert into Staff (StaffID,FirstName,LastName,TrainingCredits,StaffTypeCode,phone)
218values (44444,'Adam','Scraper',15,2,'(780) 111-5551')
219Insert into Staff (StaffID,FirstName,LastName,TrainingCredits,StaffTypeCode,phone)
220values (55555,'Bob','LaBuilder',21,3,'(780) 111-6666')
221
222Insert into Training (TrainingID, Description, Credits)
223values ('Paint101','Introduction to Painting',3)
224Insert into Training (TrainingID, Description, Credits)
225values ('Finishing123','Sanding and Finishing',6)
226Insert into Training (TrainingID, Description, Credits)
227values ('Cleaning224','Cleaning and Clearing',3)
228Insert into Training (TrainingID, Description, Credits)
229values ('Demolition101','Making Things go Boom!',4)
230Insert into Training (TrainingID, Description, Credits)
231values ('Safety104','Basic Safety Protocols',3)
232Insert into Training (TrainingID, Description, Credits)
233values ('Repairs105','Basic Repairs',3)
234Insert into Training (TrainingID, Description, Credits)
235values ('ClientConflict202','Dealing with Grumpy Clients',6)
236Insert into Training (TrainingID, Description, Credits)
237values ('Building321','Basic Building Concepts',6)
238
239
240Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
241values (11111,'Paint101','Jan 1 2016')
242Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
243values (11111,'Safety104','Jan 4 2016')
244Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
245values (11111,'ClientConflict202','Jan 7 2016')
246
247Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
248values (22222,'Cleaning224','Jan 1 2016')
249Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
250values (22222,'Safety104','Jan 4 2016')
251Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
252values (22222,'ClientConflict202','Jan 7 2016')
253
254Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
255values (33333,'Demolition101','Jan 1 2016')
256Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
257values (33333,'Safety104','Jan 4 2016')
258Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
259values (33333,'ClientConflict202','Jan 7 2016')
260
261Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
262values (44444,'Finishing123','Jan 1 2016')
263Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
264values (44444,'Safety104','Jan 4 2016')
265Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
266values (44444,'ClientConflict202','Jan 7 2016')
267
268Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
269values (55555,'Building321','Jan 1 2016')
270Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
271values (55555,'Safety104','Jan 4 2016')
272Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
273values (55555,'ClientConflict202','Jan 7 2016')
274Insert into StaffTraining(StaffID, TrainingID, CompletionDate)
275values (55555,'Finishing123','Jan 7 2016')
276
277Insert into Client (FirstName, LastName, Phone)
278values ('Peggy', 'Sue','(780) 111-1111')
279Insert into Client (FirstName, LastName, Phone)
280values ('Maggie', 'May','(780) 222-2222')
281Insert into Client (FirstName, LastName, Phone)
282values ('Billy', 'Jean','(780) 111-1111')
283Insert into Client (FirstName, LastName, Phone)
284values ('Bobby', 'McGee','(780) 111-1111')
285Insert into Client (FirstName, LastName, Phone)
286values ('Tom', 'Dooly','(780) 111-1111')
287Insert into Client (FirstName, LastName, Phone)
288values ('Mary', 'Jane','(780) 111-1111')
289Insert into Client (FirstName, LastName, Phone)
290values ('Jimmy', 'Mack','(780) 111-1111')
291Insert into Client (FirstName, LastName, Phone)
292values ('Eleanor', 'Rigby','(780) 111-1111')
293
294Insert Into Service (ServiceCode,Description,CostPerHour)
295values ('Prime1','Priming',20)
296Insert Into Service (ServiceCode,Description,CostPerHour)
297values ('Painting1','Painting',20)
298Insert Into Service (ServiceCode,Description,CostPerHour)
299values ('Construction10','Basic Reconstruction',40)
300Insert Into Service (ServiceCode,Description,CostPerHour)
301values ('Construction20','Advanced Construction',60)
302Insert Into Service (ServiceCode,Description,CostPerHour)
303values ('Demolition','Demolition of Property',30)
304Insert Into Service (ServiceCode,Description,CostPerHour)
305values ('Sanding1','Surface Sanding',20)
306Insert Into Service (ServiceCode,Description,CostPerHour)
307values ('Cleaning1','Basic Cleaning',20)
308Insert Into Service (ServiceCode,Description,CostPerHour)
309values ('Garbage1','Removal of Graffit Refuse',25)
310
311
312
313
314Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
315values ('Jan 1 2016','12345 Anywhere Street','Edmonton', 'AB','T3D 1S5',120,26,126,11111,1)
316Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
317values (1,'Prime1','Prime fence beside the house',3,20,60)
318Insert Into JobService (JobNumber, ServiceCode, Notes,hours, ActualCostPerHour, extcost)
319values (1,'Painting1','Prime fence beside the house',3,20,60)
320
321Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
322values ('Jan 2 2016','12345 First Street','Edmonton', 'AB','T3A 1A5',160,8,168,11111,1)
323Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
324values (2,'Sanding1','Sand the wall',5,20,100)
325Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
326values (2,'Painting1','Paint the wall',3,20,60)
327
328Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
329values ('Jan 3 2016','12345 Second Street','Edmonton', 'AB','T3B 1B5',20,1,21,11111,1)
330Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
331values (3,'Painting1','Paint the door',1,20,20)
332
333Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
334values ('Jan 3 2016','12345 Third Street','Edmonton', 'AB','T3C 1C5',40,2,42,22222,2)
335Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
336values (4,'Cleaning1','Clean the wall',2,20,40)
337
338
339Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
340values ('Jan 4 2016','12345 Fourth Street','Edmonton', 'AB','T3D 1D5',80,4,84,22222,2)
341Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
342values (5,'Cleaning1','Clean the fence',4,20,80)
343
344
345Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
346values ('February 5 2016','12345 Fifth Street','Edmonton', 'AB','T3E 1E5',40,2,42,11111,3)
347Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
348values (6,'Painting1','Paint the sign',2,20,40)
349
350
351Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
352values ('Jan 6 2016','12345 Sixth Street','Edmonton', 'AB','T3F 1F5',60,3,63,33333,5)
353Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
354values (7,'Demolition','Take down the sign',2,30,60)
355
356
357Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
358values ('Jan 7 2016','12345 Seventh Street','Edmonton', 'AB','T3G 1G5',110,5.5,115.5,33333,1)
359Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
360values (8,'Demolition','Clean the wall',2,30,60)
361Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
362values (8,'Garbage1','Clean the wall',2,25,50)
363
364Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
365values ('February 8 2016','12345 Eighth Street','Edmonton', 'AB','T3H 1H5',40,2,42,44444,6)
366Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
367values (9,'Sanding1','Sand the blue fence',2,20,40)
368
369
370Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
371values ('February 27 2016','12345 Ninth Street','Edmonton', 'AB','T3I 1I5',20,1,21,44444,7)
372Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
373values (10,'Sanding1','Sand the table',1,20,20)
374
375
376Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
377values ('February 28 2016','12345 Tenth Street','Edmonton', 'AB','T3J 1J5',200,10,210,55555,8)
378Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
379values (11,'Construction10','Rebiuld Table',5,40,200)
380
381
382Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
383values ('March 1 2016','12345 Eleventh Street','Edmonton', 'AB','T3K 1K5',600,30,630,55555,3)
384Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
385values (12,'Construction20','Build new shed',10,60,600)
386
387
388Insert into Job (Date, Address, City,Province,PostalCode,SubTotal,GST,Total,StaffID,ClientID)
389values ('March 3 2016','12345 Twelth Street','Edmonton', 'AB','T3L 1L5',120,6,126,11111,8)
390Insert Into JobService (JobNumber, ServiceCode, Notes, Hours,ActualCostPerHour, extcost)
391values (13,'Cleaning1','Clean the whole house!',12,10,120)
392
393
394Insert Into SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
395values ('P5','5 Gallon Paint Products','A101')
396Insert Into SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
397values ('P10','10 Gallon Paint Products','A101')
398Insert Into SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
399values ('P20','20 Gallon Paint Products','A105')
400Insert Into SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
401values ('S101','Sanding Supplies','S101')
402Insert Into SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
403values ('B123','Demolition Equipment','DM212')
404Insert Into SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
405values ('CL10','All Cleaning Supplies','B232')
406Insert Into SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
407values ('B100','Building and Construction Materials','B202')
408Insert Into SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
409values ('PR100','Primers','A211')
410Insert Into SupplyCategory (SupplyCategoryCode, Description, StorageRoom)
411values ('W100','Water','W211')
412
413
414Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
415values ('PR104','Grey Primer','PR100')
416Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
417values ('RP5','5 Gallon RedPaint','P5')
418Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
419values ('BP10','10 Gallon Blue Paint','P10')
420Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
421values ('GP20','20 Gallon Green Paint','P20')
422Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
423values ('SP10','Sanding Paper','S101')
424Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
425values ('PR105','White Primer','PR100')
426Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
427values ('PR106','Black Primer','PR100')
428Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
429values ('D100','Large Hammer','B123')
430Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
431values ('DS100','Drywall Saw','B123')
432Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
433values ('L100','Ladder','B100')
434Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
435values ('N100','Nails','B100')
436Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
437values ('C100','Soap','CL10')
438Insert Into Supply (SupplyCode,Description, SupplyCategoryCode)
439values ('S100','Sponge','CL10')
440
441Insert into JobSupply (JobNumber,SupplyCode,Quantity)
442values(1,'BP10',2)
443Insert into JobSupply (JobNumber,SupplyCode,Quantity)
444values(1,'PR104',2)
445Insert into JobSupply (JobNumber,SupplyCode,Quantity)
446values(2,'PR104',2)
447Insert into JobSupply (JobNumber,SupplyCode,Quantity)
448values(2,'SP10',4)
449Insert into JobSupply (JobNumber,SupplyCode,Quantity)
450values(3,'RP5',1)
451Insert into JobSupply (JobNumber,SupplyCode,Quantity)
452values(4,'C100',2)
453Insert into JobSupply (JobNumber,SupplyCode,Quantity)
454values(4,'S100',2)
455Insert into JobSupply (JobNumber,SupplyCode,Quantity)
456values(5,'C100',2)
457Insert into JobSupply (JobNumber,SupplyCode,Quantity)
458values(5,'S100',2)
459Insert into JobSupply (JobNumber,SupplyCode,Quantity)
460values(6,'RP5',2)
461Insert into JobSupply (JobNumber,SupplyCode,Quantity)
462values(7,'DS100',1)
463Insert into JobSupply (JobNumber,SupplyCode,Quantity)
464values(8,'D100',2)
465Insert into JobSupply (JobNumber,SupplyCode,Quantity)
466values(9,'SP10',2)
467Insert into JobSupply (JobNumber,SupplyCode,Quantity)
468values(10,'SP10',2)
469Insert into JobSupply (JobNumber,SupplyCode,Quantity)
470values(11,'D100',2)
471Insert into JobSupply (JobNumber,SupplyCode,Quantity)
472values(11,'N100',2)
473Insert into JobSupply (JobNumber,SupplyCode,Quantity)
474values(12,'L100',2)
475Insert into JobSupply (JobNumber,SupplyCode,Quantity)
476values(12,'D100',2)
477Insert into JobSupply (JobNumber,SupplyCode,Quantity)
478values(12,'N100',2)
479Insert into JobSupply (JobNumber,SupplyCode,Quantity)
480values(13,'C100',2)
481Insert into JobSupply (JobNumber,SupplyCode,Quantity)
482values(13,'S100',2)
483
484Select * from JobService
485Select * from  Service
486Select * from  JobSupply
487Select * from  Supply
488Select * from  SupplyCategory
489Select * from  Job
490Select * from  Client
491Select * from  StaffTraining
492Select * from  Training
493Select * from  Staff
494Select * from  StaffType
495
496--1
497Create Procedure AddClient (@FirstName varchar(10) = null, @LastName varchar(50) = null, @Phone char (14) not null)
498as
499If @FirstName is null or @LastName is null or @Phone is null
500	Begin
501	RaisError ('You must provide a FirstName, LastName, and Phone number',16,1)
502	end
503Else
504	Begin
505	Insert into Client (FirstName, LastName, Phone)
506	values (@FirstName, @LastName, @Phone)
507	select ClientID from Client where FirstName = @FirstName
508	End
509Return
510go
511
512--2
513Create Procedure UpdateTraining (@TrainingID varchar(10) = null, @Description varchar(50) = null, @Credits char (14) not null)
514as
515If @TrainingID is null or @Description is null or @Credits is null
516	Begin
517	RaisError ('You must provide a FirstName, LastName, and Phone number',16,1)
518	end
519If not exists (select * from Training where TrainingID = @TrainingID)
520	Begin
521	RaisError ('That training is not available.', 16, 1)
522	end
523If exists (select * from Training where Description = @Description)
524	Begin
525	RaisError ('Training description already exists.')
526	end
527Else
528	Begin
529	Update Training
530	Set Description = @Description, Credits = @Credits
531	where TrainingID = @TrainingID
532	End
533Return
534go
535
536-----Use this for above
537Create Procedure UpdateClub (@ClubID varchar(10) = null, @Clubname varchar(50) = null)
538as
539If @ClubID is null or @Clubname is null
540	Begin
541	RaisError ('You must provide a clubID and clubname',16,1)
542	end
543Else
544	Begin
545	If not exists (select * from Club where ClubId = @ClubID)
546		Begin
547		RaisError('That Club does not exist',16,1)
548		End
549	Else
550		Begin
551		update Club
552		set ClubName = @Clubname
553		where ClubId = @ClubID
554		If @@ERROR <>0
555			Begin
556			RaisError ('update failed!',16,1)
557			End
558		End
559	End
560Return
561
562--3
563Create Procedure DeleteSupplyCategory (@SupplyCategoryCode varchar (5) = null)
564as
565If @SupplyCategoryCode is null
566	Begin
567	RaisError ('You must provide a SupplyCategoryCode',16,1)
568	end
569If not exists (select * from SupplyCategory where SupplyCategoryCode = @SupplyCategoryCode)
570	Begin
571	RaisError ('This Supply Category Code is not in the database.',16,1)
572	end
573If exists (select * from SupplyCategory inner join supply on supply.supplycategorycode = supplycategory.supplycategorycode)
574	Begin
575	RaisError ('A supply currently has this supply category code!',16,1)
576	end
577Else
578	Begin
579	delete supplycategory where supplycategorycode = @SupplyCategoryCode
580	end
581Return
582
583--4
584Create Procedure LookUpStaffTraining (@StaffID int = null)
585as
586if @StaffID is null
587	Begin
588	RaisError ('You must provide a Staff ID',16,1)
589	end
590if not exists (select * from Staff where StaffID = @StaffID)
591	Begin 
592	RaisError ('That Staff ID does not exist',16,1)
593	end
594else
595	Begin
596	select distinct Firstname + " " + LastName 'Name', TrainingID, CompletionDate, Description, Credits from Staff 
597	inner join StaffTraining on Staff.StaffID = StaffTraining.StaffID
598	where StaffID = @StaffID
599	end
600return
601
602--5
603Create Procedure NoJobs()
604as
605	Begin
606	select FirstName, LastName, Phone from Client where jobid is null in (select JobNumber from Job)
607	end
608return
609
610--6
611Create Procedure LookUpStaff (@LastName varchar (50) = null)
612as
613if @LastName is null
614	Begin
615	RaisError ('You must provide a Last Name', 16,1)
616	end
617else
618	Begin
619	select StaffID, Firstname, Lastname, TrainingCredits, Description from Staff inner join StaffType on Staff.StaffID = StaffType.StaffID where Staff.Lastname like ('%@LastName')
620	end
621return
622
623--7 needs ActualCostPerHour ExtCost but there's no constant?
624Create Procedure AddJobService (@JobNumber int = null, @ServiceCode varchar (15) = null, @Notes varchar (200) = null, Hours int = null)
625as
626if @JobNumber is null or @ServiceCode is null or @Notes is null or @Hours is null
627	Begin
628	RaisError ('You must provide all information',16,1)
629	end
630if not exists (select * from Job where JobNumber = @JobNumber)
631	Begin
632	RaisError ('That job does not exist', 16,1)
633	end
634if exists (select * from Job where JobNumber = @JobNumber)
635	Begin
636	Insert into JobService (JobNumber, ServiceCode, Notes, Hours)
637	values (@JobNumber, @ServiceCode, @Notes, @Hours)
638	end
639return
640
641--8
642Alter Procedure AddJobService (