CREATE PROCEDURE AddJobService (@JobNumber INT = NULL, @ServiceCode VARCHAR (15) = NULL, @Notes VARCHAR (200) = NULL, Hours INT = NULL)
AS
DECLARE @Act INT
DECLARE @Ext INT
DECLARE @NewSub INT
IF @JobNumber IS NULL OR @ServiceCode IS NULL OR @Notes IS NULL OR @Hours IS NULL
    BEGIN
    RaisError ('You must provide all information',16,1)
    END
IF EXISTS (SELECT JobNumber FROM Job WHERE JobNumber = @JobNumber)
    BEGIN
    SET @Act = SELECT CostPerHour FROM JobService WHERE JobNumber = @JobNumber
    SET @Ext = @Act * @Hours
    INSERT INTO JobService (JobNumber, ServiceCode, Notes, Hours, ActualCostPerHour, Extcost)
    VALUES (@JobNumber, @ServiceCode, @Notes, @Hours, @Act, @Ext)
    SET @NewSub = @Ext + (@Ext * 0.05)
    END
ELSE
    BEGIN
    RaisError ('That job does not exist', 16,1)
    END
RETURN
GO