title: Cannot Execute SQL Server SP with Named and output parameters using EF Core
body: <p>I'm having trouble trying to execute a Stored Procedure from SQL Server using Entity Framework Core. This SP is used to integrate external data with a client ERP (Microsoft Dynamics Great Plains) through Econnect.</p>
  
  <p>This is the SP metadata (I only include the SP DDL line due to body limitation):</p>
  
  <pre><code>SET ANSI_NULLS ON 
  SET QUOTED_IDENTIFIER OFF 
  GO 
  create procedure dbo.taIVMultibinBinToBinTransfer @I_vITEMNMBR char(30), @I_vLOCNCODE char(10), @I_vQTYTYPE smallint = 1, @I_vUOFM char(8) = '', @I_vBIN char(15), @I_vQUANTITY numeric(19, 5), @I_vTOBIN char(15), @I_vCreateBin tinyint = 0, @I_vResetQty tinyint = 0, @I_vSerLotNmbr char(20) = '', @I_vEXPNDATE datetime = '', @I_vRequesterTrx smallint = 0, @I_vUSRDEFND1 char(50) = '', @I_vUSRDEFND2 char(50) = '', @I_vUSRDEFND3 char(50) = '', @I_vUSRDEFND4 varchar(8000) = '', @I_vUSRDEFND5 varchar(8000) = '', @O_iErrorState int output, @oErrString varchar(255) output with encryption as 
  </code></pre>
  
  <p>This is the code I'm trying to use to run the SP:</p>
  
  <pre><code>        private EconnectSpExecutionResult MakeGpTransferBetweenBins(ColonyFuelTransferDto colonyFuelTransferDb)
      {
          var spExecutionResult = new EconnectSpExecutionResult();
          var spExecQuery = "EXECUTE [dbo].[taIVMultibinBinToBinTransfer] " +
                              "@I_vITEMNMBR" +
                              ", @I_vLOCNCODE" +
                              ", @I_vUOFM" +
                              ", @I_vBIN" +
                              ", @I_vQUANTITY" +
                              ", @I_vTOBIN" +
                              ", @O_iErrorState OUTPUT" +
                              ", @oErrString OUTPUT";
  
          var fuelItemIdParam = new SqlParameter("@I_vITEMNMBR", colonyFuelTransferDb.FuelItemId);
          var locationGpIdParam = new SqlParameter("@I_vLOCNCODE", colonyFuelTransferDb.LocationGpId);
          var measureUnitIdParam = new SqlParameter("@I_vUOFM", colonyFuelTransferDb.MeasureUnitId);
          var sourceBinIdParam = new SqlParameter("@I_vBIN", colonyFuelTransferDb.SourceBinId);
          var quantityParam = new SqlParameter("@I_vQUANTITY", colonyFuelTransferDb.Quantity);
          var destinationBinIdParam = new SqlParameter("@I_vTOBIN", colonyFuelTransferDb.DestinationBinId);
          var errorNumberParam = new SqlParameter("@O_iErrorState", spExecutionResult.ErrorNumber) { Direction = System.Data.ParameterDirection.Output };
          var errorDescriptionParam = new SqlParameter("@oErrString", spExecutionResult.ErrorDescription) { Direction = System.Data.ParameterDirection.Output, Size = 255 };
  
          _seaboardGpDbContext
                  .Database
                  .ExecuteSqlCommand
                  (
                      spExecQuery,
                      fuelItemIdParam,
                      locationGpIdParam,
                      measureUnitIdParam,
                      sourceBinIdParam,
                      quantityParam,
                      destinationBinIdParam,
                      errorNumberParam,
                      errorDescriptionParam
                  );
  
          return spExecutionResult;
      }
  </code></pre>
  
  <p>The thing is that I can't get it working. The SP has several parameters that I would like to avoid  and let them take the default values specified in the metadata. Also, this SP has two output parameters that I need to retrieve after the execution, that gives you information about the errors that can occur during the integration.</p>
  
  <p>The current error that I'm getting is the following one: <strong>Error converting data type nvarchar to smallint.</strong>. It seems that EF Core is not using named parameters as we can see in the query that is actually runned:</p>
  
  <pre><code>declare @p9 int
  set @p9=NULL
  declare @p10 nvarchar(255)
  set @p10=NULL
  exec sp_executesql N'EXECUTE [dbo].[taIVMultibinBinToBinTransfer] @I_vITEMNMBR, @I_vLOCNCODE, @I_vUOFM, @I_vBIN, @I_vQUANTITY, @I_vTOBIN, @O_iErrorState OUTPUT, @oErrString OUTPUT',N'@I_vITEMNMBR nvarchar(9),@I_vLOCNCODE nvarchar(3),@I_vUOFM nvarchar(2),@I_vBIN nvarchar(3),@I_vQUANTITY decimal(6,2),@I_vTOBIN nvarchar(3),@O_iErrorState int output,@oErrString nvarchar(255) output',@I_vITEMNMBR=N'092001122',@I_vLOCNCODE=N'CZE',@I_vUOFM=N'LT',@I_vBIN=N'CZE',@I_vQUANTITY=1007.00,@I_vTOBIN=N'CHZ',@O_iErrorState=@p9 output,@oErrString=@p10 output
  select @p9, @p10
  </code></pre>
  
  <p>I would appreciate any guidance or help that you can give me. I have tried several options but none of them worked. I always get an exception related with entity framework core or sql server engine before executing the SP.</p>
  
  <p>Thank you.</p>