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?