How to retrieve stored procedure return value with Dapper

Viewed 4255

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?

2 Answers
Related