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 stackoverflow.com/a/6871382/34092 help?

mjwills
– mjwills

2019-09-17 14:05:17 +00:00
Commented Sep 17, 2019 at 14:05 — mjwills
– mjwills, Commented Sep 17, 2019 at 14:05

David Browne - Microsoft · Accepted Answer · 2019-09-17 14:08:29Z

3

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

answered Sep 17, 2019 at 14:08

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

André Reichelt Over a year ago

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.

David Browne - Microsoft Over a year ago

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.

Collectives™ on Stack Overflow

Out parameter with ExecuteSqlCommandAsync

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related