0

I have two tables:

  1. Project with fields fld_id (integer), fld_allocated_hours (integer), fld_project_id (integer)
  2. Timesheet with columns fld_id (integer), fld_allocated_time (integer), fld_project_id (integer).

And I have this procedure:

CREATE PROCEDURE sp_insert_allocated_time(p_project_id integer, p_allocated_time integer)
  RETURNS void 
  AS
BEGIN   
INSERT INTO dbo.Timesheet(fld_id, fld_project_id,fld_allocated_time)
from
(
  SELECT p.fld_id, p.fld_allocated_hours t.fld_allocated_time
  FROM dbo.Project p
  INNER JOIN dbo.Timesheet t
  ON p.fld_id=t.fld_id
  where t.fld_project_id = p_project_id
)AS Alias
GROUP BY fld_id, fld_allocated_days, fld_allocated_time
having SUM(fld_allocated_time) < fld_allocated_hours;
END;

All I want to make this procedure to insert the allocated_time value into the table Timesheet using the parameter fld_project_id if SUM(fld_allocated_time) from the table Timesheet is smaller or equal to fld_allocated_hours from the table Project, otherwise show an error message and exit.

How can I do this? I think my procedure code is not even close to what I want to do.

Thanks.

1
CREATE PROCEDURE sp_insert_allocated_time @p_project_id INT, @p_allocated_time INT
AS

BEGIN

DECLARE @sum_alloc_time int, @alloc_hours int, @fld_id int

select @sum_alloc_time = SUM(fld_allocated_time)
from dbo.Timesheet
where fld_project_id = @p_project_id;

select @alloc_hours = p.fld_allocated_hours, @fld_id = p.fld_id
FROM dbo.Project p
INNER JOIN dbo.Timesheet t
ON p.fld_id=t.fld_id
where t.fld_project_id = @p_project_id;

IF @sum_alloc_time <= @alloc_hours
BEGIN
BEGIN TRAN;
INSERT INTO dbo.Timesheet(fld_id, fld_project_id,fld_allocated_time)
VALUES (@fld_id, @p_project_id, @p_allocated_time);
COMMIT TRAN;
END;

END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.