Pass a multi-value to a stored procedure ? I got an error "Subquery returned more than 1 value.-CodePudding

I am trying to pass a multi-value to a stored procedure from my C# code.

I created this stored procedure:

CREATE PROCEDURE [dbo].[RepCharpenteProductionByShiftTime]
    @FirstDate DATE,
    @LastDate DATE,
    @ShiftTime NVARCHAR(100)
AS
    SELECT 
        proinfo.ProjectN AS 'BS',
        cust.Name AS 'Client',
        proinfo.ProjectName AS 'Projet',
        SUM(ProShip.[Weight] * [Quantity]) AS 'Poids Total'
    FROM 
        [dbo].[DailyWeldingPaintProduction] daiPaiPro
    INNER JOIN
        ProjectShipping ProShip ON ProShip.id = [FK_idPartShip]
    INNER JOIN
        ProjectInfo proinfo ON proinfo.id = ProShip.IdProject
    INNER JOIN 
        Customer cust ON cust.ID = proinfo.FK_Customer
    LEFT JOIN
        PreviousWeight prev ON prev.FK_Project = proinfo.id
    WHERE 
        FK_idNextProcess = 13 
        AND CONVERT(DATE, [CreationDate]) BETWEEN @FirstDate AND @LastDate 
        AND FK_ShiftTime IN (SELECT value 
                             FROM STRING_SPLIT(@ShiftTime, ','))
    GROUP BY
        proinfo.ProjectN, cust.Name, proinfo.ProjectName,
        PreviousWeight, proinfo.Weight
    ORDER BY
        proinfo.ProjectN

At this point everything works fine but when I changed this line of code

FK_ShiftTime IN (SELECT value FROM STRING_SPLIT(@ShiftTime, ','))

so I can get all results when the value of @ShiftTime is null to be like this

FK_ShiftTime IN (IIF(@ShiftTime IS NULL, daiPaiPro.FK_ShiftTime,(SELECT value FROM STRING_SPLIT(@ShiftTime, ','))))

then I got this error message

Msg 512, Level 16, State 1, Procedure dbo.RepCharpenteProductionByShiftTime, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Could you please tell me why I'm getting this error and how I can fix it?

CodePudding user response：

iif() expects to return a single scalar value, not a set.

Try the following instead

and (
  FK_ShiftTime in (select value from STRING_SPLIT(@ShiftTime,','))
  or (@ShiftTime is null and FK_ShiftTime = daiPaiPro.FK_ShiftTime)
)