Write to output parameter with c#

Question

In SQL Server, I can pass a value IN to a stored procedure output parameter, alter it, and then read it back:

CREATE PROCEDURE [dbo].[testSP]
    @myOutputParam INT OUTPUT
AS
BEGIN
    SELECT @myOutputParam = @myOutputParam + 1
END
GO

DECLARE @x int = 1

EXEC testSP @myOutputParam = @x OUTPUT

PRINT @x -- prints: 2

However, when I try and do this from C#, it does not work:

using (SqlConnection db = new SqlConnection("..."))
using (SqlCommand cmd = new SqlCommand("testSP", db))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@myOutputParam", SqlDbType.Int).Value = (int)1;
    cmd.Parameters["@myOutputParam"].Direction = ParameterDirection.Output;

    db.Open();

    int rowsAffected = cmd.ExecuteNonQuery();
    int x = (int)cmd.Parameters["@myOutputParam"].Value;
}

The final int x assignment throws an error

Specified cast is not valid

Where am I going wrong?

what is the result of cmd.Parameters["@myOutputParam"].Value without casting? — SᴇM
– SᴇM, Commented Sep 20, 2017 at 6:50
@SeM I think its an empty object. Its not null because the cast still fails even if I use int? x = (int?)cmd.Parameters["@myOutputParam"].Value; — mulllhausen
– mulllhausen, Commented Sep 20, 2017 at 7:05

marc_s · Accepted Answer · 2017-09-20 09:42:06Z

8

The stored procedure assigns null to the output parameter as the result of calculation because it is specified as output only.

The actual parameter direction is input and output:

cmd.Parameters["@myOutputParam"].Direction = ParameterDirection.InputOutput;

edited Sep 20, 2017 at 9:42

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Sep 20, 2017 at 6:51

Anton Ryzhov

1312 bronze badges

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

Collectives™ on Stack Overflow

Write to output parameter with c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related