2

My C# code calls a stored procedure with a return value (not a return parameter).

// C#
for (int i = 0; i < param.Length; i++)
{
    cmd.Parameters.Add(param[i]);
}
cmd.CommandType = CommandType.StoredProcedure;

int retval = cmd.ExecuteNonQuery();
con.Close();
return retval;

The stored procedure inserts a record and grabs the new record id with scope_identity().

/* SQL */
SET NOCOUNT ON;

INSERT INTO [dbo].[table] (columns)
VALUES (@parameters);


SET @iParentRowId = Scope_identity();


INSERT INTO [table_related] (ParentRowId, Column2) VALUES (@iParentRowId, @Column2Value);

RETURN @iParentRowId;

The sp works exactly as expected, but C# returns -1. (I assume that is a success indicator.)

If I SET NOCOUNT OFF, I get the number of rows affected, which I don't care about.

Can I use RETURN from the sp to get the new RowId?

Andy In NC
  • 112
  • 1
  • 8
  • In your C# command you have to define an output parameter to receive the output from the stored procedure. – squillman Jan 26 '18 at 16:12
  • You can use RETURN for this but it is often considered bad practice. The RETURN value is intended to indicate a status of the execution. When you want values returned you should use an OUTPUT parameter. – Sean Lange Jan 26 '18 at 17:07
  • Good point about the best practice. The non-zero value should indicate the success, though it's not listed in the above code. – Andy In NC Jan 26 '18 at 18:24

0 Answers0