Out parameter with ExecuteSqlCommandAsync

Question

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?

Does https://stackoverflow.com/a/6871382/34092 help? – mjwills Sep 17 '19 at 14:05 — mjwills, Sep 17 '19 at 14:05

score 3 · Accepted Answer · answered Sep 17 '19 at 14:08

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

Thank you very much. After adding `@ID_Storage = @ID_Storage OUTPUT` to the parameter list, it works fine. Can you teach me, why I need to do this weird @Param = @Param thing? I know, that it works, but I don't understand, why it is necessary. — André Reichelt, Sep 17 '19 at 14:12
That's how you bind parameters by name. To the left of `=` is the formal parameter name, and to the right is the parameter that contains the value you are passing. You can use the same name for both, which always looks wierd. If you want to bind the parameters by position instead, you can, just omit the `@param = ` part and ensure you've got the parameters in the right order. — David Browne - Microsoft, Sep 17 '19 at 14:16

Out parameter with ExecuteSqlCommandAsync

1 Answers1