How with Dapper to get the returned value of a stored procedure?

dapper sql-server tsql

Question

I have a saved process that looks like this:

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 stored procedure that I can call in C#, but I can't seem to pull the right value out of it:

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

How can I make sure thereturnCode the value returned from either the variable will beRETURN -11 or theRETURN @@ERROR lines?

1
0
5/18/2017 11:02:05 AM

Accepted Answer

To permit use of theRETURN statement in SQL, C# changes to...

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

Though not the implementation I had hoped for, it nonetheless functions.

2
5/19/2017 8:21:37 AM

Popular Answer

I advise usingSELECT to make your saved procedure's values available. On the dapper call, you need also provide a return type:

RETURN -11

becomes

SELECT -11

and the sartorial call turns into:

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

This pattern works as predicted after multiple implementations on my part.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow