How can i get @rErr / Output Parameter value in c#?

Question

I create a SQL stored procedure

create proc p1
(
@name1 nvarchar(50),
@rErr int OUTPUT
)
as
begin Transaction
    insert into test (name1)
    values (@name1)
    if  @name1 = 'n100'
        Begin
            Rollback Transaction
            Set @rErr=50001
            Return @rErr
        End
commit Transaction

How can I get the @rErr value in C#?

Answer 1

Accessing output parameters is a little awkward; you need to add them to the parameters collection in the usual way, with with a .Direction of Output. Then you can read the .Value of the parameter object after executing the method. However: it is much easier to use select and process it as a result. Note that return values can be done in a similar way, but with the appropriate .Direction. The fact that you both output and return it, in this case, makes it even more fun... I'd just use the output part, personally. Or ... throw an exception (raiserrror).

Something like:

using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = "p1";
    cmd.CommandType = CommandType.StoredProcedure;

    var name = cmd.CreateParameter();
    name.ParameterName = "@name1";
    name.Value = "abc"; // etc
    cmd.Parameters.Add(name);

    var err = cmd.CreateParameter();
    err.ParameterName = "@rErr";
    err.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(err);

    cmd.ExecuteNonQuery();
    if (err.Value is int i)
    {
        // error i happened
    }
}

However: if you'd just used:

raiserror (50001, 16, 1) -- severity needs to be at least 16 here; typically = 16

or (in more recent SQL Server versions):

throw 50001, 'oops', 1

you can get a similar result much more easily; this will result in an Exception direction from the ADO.NET layer.

(note that you should add the custom error message formally to sysmessages when using raiserror - throw doesn't require that step)

If you used the throw (or raiserror) approach, and removed the output parameter, this entire piece of code could become, with some help from Dapper:

conn.Execute("p1", new { name1 = "abc" }, commandType: CommandType.StoredProcedure);

which is a lot easier to get right!

Answer 2

You don't need transaction for a single insert, you can refactor your SP like following. To return the code as output parameter, you can simply use RETURN(50001)

CREATE PROC P1 (@name1 NVARCHAR(50), 
                @rErr  INT output) 
AS 
    IF @name1 <> 'n100' 
      BEGIN 
          INSERT INTO test(name1) 
          VALUES      (@name1) 

          RETURN(0) 
      END 

    RETURN(50001)