How to retrieve stored procedure return value with Dapper

Question

I have a stored procedure of this form:

CREATE PROCEDURE AddProduct
    (@ProductID varchar(10),
     @Name  nvarchar(150)
    )
AS
    SET NOCOUNT ON;

    IF EXISTS (SELECT TOP 1 ProductID FROM Products 
               WHERE ProductID = @ProductID)
        RETURN -11
    ELSE
    BEGIN
        INSERT INTO Products ([AgentID], [Name])
        VALUES (@AgentID, @Name)

        RETURN @@ERROR
    END

I have this C# to call the stored procedure, but I can't seem to get a correct value form it:

var returnCode = cn.Query(
    sql: "AddProduct",
    param: new { @ProductID = prodId, @Name = name },
    commandType: CommandType.StoredProcedure);

How can I ensure that the returnCode variable will contain the value returned from either the RETURN -11 or the RETURN @@ERROR lines?

Answer 1

To enable use of the RETURN statement in SQL the C# becomes...

var _params = new DynamicParameters{ @ProductID = prodId, @Name = name };
_params.Add(
    name: "@RetVal",
    dbType: DbType.Int32,
    direction: ParameterDirection.ReturnValue
    );
var returnCode = cn.Execute(
    sql: "AddProduct",
    param: _params,
    commandType: CommandType.StoredProcedure);
return _params.Get<int>("@RetVal");

It's not the implementation I was hoping for, but it works.

Answer 2

I suggest you use SELECT to return the values from your stored procedure. Also you should specify a return type on the dapper call:

RETURN -11

becomes

SELECT -11

and the dapper call becomes:

var returnCode = cn.Query<int>(..

I have implemented this pattern several times and it works as expected.