CODEWITHSUNDEEP
c#sql-serverlinq.net-coreentity-framework-core
jmrivas
jmrivas

Reputation: 137

Cannot Execute SQL Server SP with Named and output parameters using EF Core

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.

This is the SP metadata (I only include the SP DDL line due to body limitation):

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

This is the code I'm trying to use to run the SP:

        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;
    }

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.

The current error that I'm getting is the following one: Error converting data type nvarchar to smallint.. It seems that EF Core is not using named parameters as we can see in the query that is actually runned:

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

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.

Thank you.

Upvotes: 0

Views: 287

Answers (1)

Mike-314
Mike-314

Reputation: 351

The issue is that the parameters are going to be provided to the sp in the order in which they are received. You want to assign them to the sp using the names in the sp, which can be the same.

The sql executed will essentially be this:

declare @I_vITEMNMBR char(30) = .. , 
    @I_vLOCNCODE char(10) = .., 
    @I_vUOFM char(8) = .., 
    @I_vBIN char(15) = .., 
    @I_vQUANTITY numeric(19, 5) = .., 
    @I_vTOBIN char(15) = .., 
    @O_iErrorState int,
    @oErrString varchar(255)

EXECUTE [dbo].[taIVMultibinBinToBinTransfer] 
                        @I_vITEMNMBR
                        , @I_vLOCNCODE
                        , @I_vUOFM
                        , @I_vBIN
                        , @I_vQUANTITY
                        , @I_vTOBIN
                        , @O_iErrorState OUTPUT
                        , @oErrString OUTPUT

@I_vUOFM is being assigned to @I_vQTYTYPE, which is the wrong type.

what you want is this:

declare @I_vITEMNMBR char(30) = .., 
    @I_vLOCNCODE char(10) = .., 
    @I_vUOFM char(8) = .., 
    @I_vBIN char(15) = .., 
    @I_vQUANTITY numeric(19, 5) = .., 
    @I_vTOBIN char(15) = .., 
    @O_iErrorState int = ..,
    @oErrString varchar(255) = ..

EXECUTE [dbo].[taIVMultibinBinToBinTransfer] 
                        @I_vITEMNMBR  = @I_vITEMNMBR
                        , @I_vLOCNCODE = @I_vLOCNCODE
                        , @I_vUOFM = @I_vUOFM
                        , @I_vBIN = @I_vBIN
                        , @I_vQUANTITY = @I_vQUANTITY
                        , @I_vTOBIN = @I_vTOBIN
                        , @O_iErrorState = @O_iErrorState OUTPUT
                        , @oErrString = @oErrString OUTPUT

Yes, if you just want to provided those parameters, you'll have to change your code to something like this:

var spExecQuery = @"EXECUTE [dbo].[taIVMultibinBinToBinTransfer] 
                    @I_vITEMNMBR    = @I_vITEMNMBR
                  , @I_vLOCNCODE    = @I_vLOCNCODE
                  , @I_vUOFM        = @I_vUOFM
                  , @I_vBIN         = @I_vBIN
                  , @I_vQUANTITY    = @I_vQUANTITY
                  , @I_vTOBIN       = @I_vTOBIN
                  , @O_iErrorState  = @O_iErrorState OUTPUT
                  , @oErrString     = @oErrString OUTPUT";

Upvotes: 2

Related Questions