I am working on a project in Microsoft SQL Server Management Studio v17.7.

For my stored procedure listed below, I am currently getting when I run it with sample data:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

It seems that, aside from the above error, @dateDifference and @fundedBudget are not being populated during the execution of the stored procedure when I run it in the debugger.

How should I fix my code? I'm really stuck right now.

The guidelines for the stored procedure:

SP_ProcessProjectDelay

Given a project Id, this procedure finds if any max end date of any activity within the project is after the projects projected end date. If this happens, the procedure will calculate how many days it is late (use DATEDIFF) and fines the project $100 for each day late it is late.

In addition, the project tables projectedenddate will be updated with the new end date and the fundedbudget will be updated with the original funded budget plus the fines per day late.

Parameters: projectId

QUERY FILE:

create table ProjectMain

( projectId char(4) NOT NULL PRIMARY KEY, projectName varchar(50), firmFedID char(9), fundedbudget decimal(16,2), projectStartDate date, projectStatus varchar(25), projectTypeCode char(5), projectedEndDate date, projectManager char(8) ) go

create table ActivityMain ( activityId char(4) NOT NULL, activityName varchar(50), projectId char(4) NOT NULL, PRIMARY KEY(activityId, projectId), costToDate decimal(16,2), activityStatus varchar(25), startDate date, endDate date ) go

create table Firm ( firmFedID char(9) NOT NULL PRIMARY KEY, firmName varchar(50), firmAddress varchar(50) ) go

create table ProjectType ( projectTypeCode char(5) NOT NULL PRIMARY KEY, projectTypeDesc varchar(50) ) go

CREATE PROCEDURE SP_ProcessProjectDelay ( @projectId char(4) ) AS BEGIN

DECLARE @projectedEndDate AS date, @maxEndDate AS date, @dateDifference AS int, @fundedBudget AS decimal(16,2), @fines AS decimal(16,2)

SET @projectedEndDate = ( ----Return subset with matching projectId SELECT projectedEndDate FROM ProjectMain INNER JOIN ActivityMain ON ProjectMain.projectId = ActivityMain.projectId AND ProjectMain.projectId = @projectId )

SET @fundedBudget = ( ----Return subset with matching projectId SELECT fundedBudget FROM ProjectMain INNER JOIN ActivityMain ON ProjectMain.projectId = ActivityMain.projectId AND ProjectMain.projectId = @projectId ) SET @maxEndDate = ( ----Return subset with matching projectId SELECT MAX(endDate) FROM ProjectMain INNER JOIN ActivityMain ON ProjectMain.projectId = ActivityMain.projectId AND ProjectMain.projectId = @projectId ) IF @maxEndDate > @projectedEndDate SET @dateDifference = DATEDIFF(DAY, @maxEndDate, @projectedEndDate) SET @projectedEndDate = @maxEndDate SET @fines = @dateDifference * 100 SET @fundedBudget -= @fines UPDATE ProjectMain SET projectedEndDate = @projectedEndDate, fundedbudget = @fundedBudget WHERE projectId = @projectId

END

GO