CODEWITHSUNDEEP
c#sqlsql-serverentity-framework
André Reichelt
André Reichelt

Reputation: 1631

Out parameter with ExecuteSqlCommandAsync

I'm calling a stored procedure using Entity Framework's ExecuteSqlCommandAsync command. This throws an error message:

Procedure or function 'LoadChargeCarrierOnForklift' expects parameter '@ID_Storage', which was not supplied.

My code looks as follows:

SqlParameter outIdStorageParam = new SqlParameter("@ID_Storage", SqlDbType.Int) {
    Direction = ParameterDirection.Output
};

try {
    await Database.ExecuteSqlCommandAsync(
        "exec dbo.LoadChargeCarrierOnForklift @ID_Carrier, @ForkliftName, @User",
        new SqlParameter("@ID_Carrier", id_Carrier),
        new SqlParameter("@ForkliftName", forkliftName),
        new SqlParameter("@User", user),
        outIdStorageParam
    );
} catch (System.Exception ex) {
    var a = ex;
    throw;
}

return (int)outIdStorageParam.Value;

The following SQL command works, though:

USE [MyDB]
GO

DECLARE @return_value int,
        @ID_Storage int

EXEC    @return_value = [dbo].[LoadChargeCarrierOnForklift]
        @ID_Carrier = 1,
        @ForkliftName = N'ABC',
        @User = N'DEF',
        @ID_Storage = @ID_Storage OUTPUT

SELECT  @ID_Storage as N'@ID_Storage'

SELECT  'Return Value' = @return_value

GO

What am I doing wrong with the parameter from the C# code?

Upvotes: 1

Views: 841

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

This:

await Database.ExecuteSqlCommandAsync(
    "exec dbo.LoadChargeCarrierOnForklift @ID_Carrier, @ForkliftName, @User",
    new SqlParameter("@ID_Carrier", id_Carrier),
    new SqlParameter("@ForkliftName", forkliftName),
    new SqlParameter("@User", user),
    outIdStorageParam

Binds the parameters by position and omits the parameter for ID_Storage. The equivilent of

EXEC    @return_value = [dbo].[LoadChargeCarrierOnForklift]
        @ID_Carrier = 1,
        @ForkliftName = N'ABC',
        @User = N'DEF',
        @ID_Storage = @ID_Storage OUTPUT

which binds the parameters by name, and includes two output parameters, would be

    SqlParameter outIdStorageParam = new SqlParameter("@ID_Storage", SqlDbType.Int) {
        Direction = ParameterDirection.Output};
    SqlParameter outReturnValue = new SqlParameter("@return_value", SqlDbType.Int) {
        Direction = ParameterDirection.Output };

    await Database.ExecuteSqlCommandAsync(
        @"EXEC @return_value = [dbo].[LoadChargeCarrierOnForklift]
            @ID_Carrier = @ID_Carrier,
            @ForkliftName = @ForkliftName,
            @User = @User,
            @ID_Storage = @ID_Storage OUTPUT",
        outReturnValue 
        new SqlParameter("@ID_Carrier", id_Carrier),
        new SqlParameter("@ForkliftName", forkliftName),
        new SqlParameter("@User", user),
        outIdStorageParam);

Upvotes: 3

Related Questions