Must pass parameter number 2 and subsequent parameters as '@name = value'

April 20, 2022

In SQL Server, I am trying to pass more than one value to @businessArea parameter in my stored procedure. I tried it with using IN clause but it gives an error as follow (but when I pass one value to @BusinessArea parameter, it works). How can I correct it?

Any help is much appreciated.

Must pass parameter number 2 and subsequent parameters as ‘@name = value’. After the form ‘@name = value’ has been used, all subsequent parameters must be passed in the form ‘@name = value’.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.
Visit Medevel

ALTER PROCEDURE [dbo].[test1]
    @Year int = NULL,
    @BusinessArea Varchar(max) = NULL
AS
BEGIN
    SELECT DISTINCT 
        BusinessArea AS Type, 
        CAST(SUM(NetValue) AS DECIMAL(20, 2)) AS Value,
        '' as parentID 
    FROM 
        Sales
    WHERE 
        (Call_ActualStartDate >= DATEFROMPARTS(@Year, 1, 1) 
         AND Call_ActualStartDate < DATEFROMPARTS(@Year+1, 1, 1)) 
        AND (BusinessArea IN (@BusinessArea) OR @BusinessArea IS NULL)
    GROUP BY 
        BusinessArea
    ORDER BY  
        Value DESC
END
GO


EXEC test1 @BusinessArea='maho','piliyandala'

>Solution :

In Your Execute Statement

   exec test1 @BusinessArea='maho','piliyandala'

SQL Server treats maho as one input value and piliyandala is another input value.

To make it one value together you need to pass it as ‘maho,piliyandala’ and split the value based on the comma inside your stored procedure.

CREATE OR ALTER PROCEDURE [dbo].[test1]
@Year int = NULL,
@BusinessArea Varchar(max)= NULL
AS
BEGIN
SELECT DISTINCT BusinessArea as Type, Cast(sum(NetValue)AS DECIMAL(20,2)) as Value , '' as parentID 
FROM Sales
WHERE (Call_ActualStartDate >= DATEFROMPARTS(@Year, 1, 1) AND Call_ActualStartDate < DATEFROMPARTS(@Year+1, 1, 1)) 
      AND (BusinessArea IN(SELECT Value FROM string_split(@BusinessArea,',')) OR @BusinessArea IS NULL)
GROUP BY BusinessArea
ORDER BY Value DESC
END

go 

exec test1 @BusinessArea='maho,piliyandala'