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

Question

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 project’s 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 table’s “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

Explanation / Answer

If you still have any doubts, please give me comment...

It seems that table have fundedbudget column, but you are provided fundedBudget. In SQL table names are non-sensitive and column names are sensitive. So you have to give column names as like in table.

check the line:

SELECT fundedBudget
FROM ProjectMain

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

By seeing this error, you got one or more rows from subquery, for that you have to use ANY, ALL, IN etc....

=ANY(SUB QUERY)

>=ANY(SUB QUERY)

<ANY(SUB QUERY)

IN(SUB QUERY)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.