Hey I need help with the last part of this sql code. when i excecute the code i
ID: 3599914 • Letter: H
Question
Hey I need help with the last part of this sql code. when i excecute the code i get an error around @DateDiff and @projectedenddate. Could you take a look at the code i am using microsoft sql server managment studio.
create table Project
(
projectId char(4),
primary key(projectId),
projectName varchar(50),
firmFedID char(9),
fundedbudget decimal(16,2),
projectStartDate date,
projectstatus varchar(25),
projectTypeCode char(5),
projectedEndDate date,
projectManager char(8),
activityID char(4)
);
go
create table Activity
(
activityId char(4),
projectId char(4),
activityName varchar(50),
costToDate decimal(16,2),
activityStatus varchar(25),
startDate date,
endDate date
primary key(projectId,activityId)
);
go
create procedure sp_addUpdateProject
(
@projectId char(4),
@projectName varchar(50),
@firmFedID char(9),
@fundedbudget decimal(16,2),
@projectStartDate date,
@projectstatus varchar(25),
@projectTypeCode char(5),
@projectedEndDate date,
@projectManager char(8),
@activityId char(4)
)
as
begin
if exists(select * from Project where @projectId = projectId)
begin
update Project
set
projectName = @projectName,
firmFedID = @firmFedID,
fundedbudget = @fundedbudget,
projectStartDate = @projectStartDate,
projectstatus = @projectstatus,
projectTypeCode = @projectTypeCode,
projectedEndDate = @projectedEndDate,
projectManager = @projectManager,
activityId = @activityId
where @projectId = projectId;
end
else
begin
insert into Project(projectId, projectName, firmFedID, fundedBudget, projectStartDate,
projectStatus, projectTypeCode, projectedEndDate, projectManager, activityID)
values (
@projectId,
@projectName,
@firmFedID,
@fundedbudget,
@projectStartDate,
@projectstatus,
@projectTypeCode,
@projectedEndDate,
@projectManager,
@activityId);
end
end
Go
create procedure SP_DeleteProject
(
@projectId char(4)
)
as
begin
delete from Project where projectId = @projectId;
end
go
create procedure sp_addUpdateActivity
(
@activityId char(4),
@activityName varchar(50),
@costToDate decimal(16,2),
@activityStatus varchar(25),
@startDate date,
@endDate date
)
as
begin
if exists(select * from Activity where activityId = @activityId)
begin
update Activity
set
activityName = @activityName,
costToDate = @costToDate,
activityStatus = @activityStatus,
startDate = @startDate,
endDate = @endDate
where @activityId = activityId;
end
else
begin
insert into Activity(activityId, activityName, costToDate, activityStatus, startDate, endDate)
values(
@activityId,
@activityName,
@costToDate,
@activityStatus,
@startDate,
@endDate)
end
end
go
create procedure SP_DeleteActivity
(
@projectId char(4),
@activityId char(4)
)
as
begin
delete from Activity where projectId = @projectId and activityId = @activityId;
end
go
use [project 2]
go
create procedure SP_ProcessProjectDelay
(
@projectId char(4),
@DateDiff Int,
@projectedEndDate date,
@actualEndDate date
)
as
begin
select @projectedEndDate = projectedEndDate from Project where projectId = @projectId
select @actualEndDate = endDate from Activity where projectId = @projectId order by endDate
@DateDiff = datediff(@projectedEndDate, @actualenddate)
if(@DateDiff < 0)
begin
update Activity set endDate = @actualenddate where projectId = @projectId
end
end
Explanation / Answer
the code is fixed and modified
create table Project
(
projectId char(4),
primary key(projectId),
projectName varchar(50),
firmFedID char(9),
fundedbudget decimal(16,2),
projectStartDate date,
projectstatus varchar(25),
projectTypeCode char(5),
projectedEndDate date,
projectManager char(8),
activityID char(4)
);
go
create table Activity
(
activityId char(4),
projectId char(4),
activityName varchar(50),
costToDate decimal(16,2),
activityStatus varchar(25),
startDate date,
endDate date
primary key(projectId,activityId)
);
go
create procedure sp_addUpdateProject
(
@projectId char(4),
@projectName varchar(50),
@firmFedID char(9),
@fundedbudget decimal(16,2),
@projectStartDate date,
@projectstatus varchar(25),
@projectTypeCode char(5),
@projectedEndDate date,
@projectManager char(8),
@activityId char(4)
)
as
begin
if exists(select * from Project where @projectId = projectId)
begin
update Project
set
projectName = @projectName,
firmFedID = @firmFedID,
fundedbudget = @fundedbudget,
projectStartDate = @projectStartDate,
projectstatus = @projectstatus,
projectTypeCode = @projectTypeCode,
projectedEndDate = @projectedEndDate,
projectManager = @projectManager,
activityId = @activityId
where @projectId = projectId;
end
else
begin
insert into Project(projectId, projectName, firmFedID, fundedBudget, projectStartDate,
projectStatus, projectTypeCode, projectedEndDate, projectManager, activityID)
values (
@projectId,
@projectName,
@firmFedID,
@fundedbudget,
@projectStartDate,
@projectstatus,
@projectTypeCode,
@projectedEndDate,
@projectManager,
@activityId);
end
end
Go
create procedure SP_DeleteProject
(
@projectId char(4)
)
as
begin
delete from Project where projectId = @projectId;
end
go
create procedure sp_addUpdateActivity
(
@activityId char(4),
@activityName varchar(50),
@costToDate decimal(16,2),
@activityStatus varchar(25),
@startDate date,
@endDate date
)
as
begin
if exists(select * from Activity where activityId = @activityId)
begin
update Activity
set
activityName = @activityName,
costToDate = @costToDate,
activityStatus = @activityStatus,
startDate = @startDate,
endDate = @endDate
where @activityId = activityId;
end
else
begin
insert into Activity(activityId, activityName, costToDate, activityStatus, startDate, endDate)
values(
@activityId,
@activityName,
@costToDate,
@activityStatus,
@startDate,
@endDate)
end
end
go
create procedure SP_DeleteActivity
(
@projectId char(4),
@activityId char(4)
)
as
begin
delete from Activity where projectId = @projectId and activityId = @activityId;
end
go
use [project 2]
go
create procedure SP_ProcessProjectDelay
(
@projectId char(4),
@DateDiff Int,
@projectedEndDate date,
@actualEndDate date
)
as
begin
select @projectedEndDate = projectedEndDate from Project where projectId = @projectId
select @actualEndDate = endDate from Activity where projectId = @projectId order by endDate
@DateDiff = datediff(@projectedEndDate, @actualenddate)
if(@DateDiff < 0)
begin
update Activity set endDate = @actualenddate where projectId = @projectId
end
end