stored procedure confusing named parameters 'Error converting data type nvarchar to int.'

Question

Running the exact same sql command, I get an error depending on what order the parameters are defined in the stored proc. This error was originally encountered using a stored proc mapped through entity framework, but that does not seem to be the cause of the issue.

The error message 'Error converting data type nvarchar to int.' makes it seem like the sproc is trying to jam the @CagIdList parameter into one of the nullable int parameters. Thoughts?

Sql command:

exec sp_executesql 
    N'rptAll.usp_SprocParameterTest @StartDate, @EndDate, @CAGIdList',
    N'@StartDate datetime,@EndDate datetime,@CAGIdList nvarchar(1317)',
    @StartDate='2014-11-16 00:00:00',@EndDate='2014-12-16 00:00:00',@CAGIdList=N'857,858,859'

The above command will fail with this stored proc:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [rptAll].[usp_SprocParameterTest]
(
        @StartDate datetime,
        @EndDate datetime,
        @StartRow int = null, -- please note where this parameter started
        @MaxRows int = null, -- me too
        @Sort varchar(255)= null,
        @mfgCode varchar(255) = null,
        @CAGIdList varchar(max) = null
)
as

select 1

The same will succeed for this stored proc :

--Move the nullable int params to the end of the list
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [rptAll].[usp_SprocParameterTest]
(
        @StartDate datetime,
        @EndDate datetime,
        @Sort varchar(255)= null,
        @mfgCode varchar(255) = null,
        @CAGIdList varchar(5000) = null,
        @StartRow int = null, --look at mee
        @MaxRows int = null --hey me too
)
as

select 1

Answer 1

That's because you are calling the procedure and providing values for the three first parameters, regardless of their names. The parameter names that you use in the query have no relation to the parameter names in the procedure.

If you want to specify parameter values for specific parameters, you have to name them:

rptAll.usp_SprocParameterTest @StartDate = @StartDate, @EndDate = @EndDate, @CAGIdList = @CAGIdList

---

This is the same difference as calling a procedure without parameter names:

rptAll.usp_SprocParameterTest '2014-11-16 00:00:00', '2014-12-16 00:00:00', N'857,858,859'

and with parameter names:

rptAll.usp_SprocParameterTest @StartDate = '2014-11-16 00:00:00', @EndDate  = '2014-12-16 00:00:00', @CAGIdList = N'857,858,859'