Get return value from stored procedure using ExecuteSqlCommand (using Entity Framework)

Question

I have code that looks like this:

int retval = databaseContext.Database.ExecuteSqlCommand(command, sqlParameters.ToArray());

Where databaseContext is of type System.Data.Entity.DbContext

I want to use the return value to know whether or not the stored procedure ran successfuly. Based on the documentation ExecuteSqlCommand should return the result of the stored procedure.

However, The command always returns -1 to retval, no matter what I set the stored procedure to do. I've tried returning various integers and even calling RAISERROR in the stored procedure, but the return value is consistently -1.

Here are two stored procs I tried but they both returned -1 and did not give any indication of whether it ran successfully or not:

CREATE PROCEDURE [dbo].[myproc]
AS
BEGIN
      RAISERROR ('You fail', -- Message text.
                       0, -- Severity - operation failed.
                       500-- State.
                       );
END
GO

CREATE PROCEDURE [dbo].[myproc]
AS
BEGIN
      RETURN 1
END
GO

Any idea what I'm doing wrong here?

Possible duplicate of [Get return value from stored procedure](http://stackoverflow.com/questions/14735477/get-return-value-from-stored-procedure) — DavidG, Nov 17 '15 at 01:04
@DavidG, no this is a different issue. That question requires the query to be in a particular format, while the ExecuteSqlCommand documentation doesn't seem to require any such thing — Zain Rizvi, Nov 17 '15 at 01:07
If you want the return value, you have to use that method. `ExecuteSqlCommand` won't do it for you, unless possibly something `SELECT`s the value into a result set. — DavidG, Nov 17 '15 at 01:09
@DavidG, ah, I see. I had seen that answer you linked but hadn't understood the difference. If you want to post your comment as an answer I'll accept it — Zain Rizvi, Nov 17 '15 at 01:23

score 7 · Accepted Answer · edited May 23 '17 at 10:27

7

Based on DavidG's comment:

ExecuteSqlCommand only returns values if it's the result of a select statement.

If you want the return value of a stored procedure (like shown in the question), then you have to use the EXEC @ReturnValue = [StoredProc] @param1 ... method as described in this SO answer.

edited May 23 '17 at 10:27

Community

1
1

answered Nov 17 '15 at 02:33

Zain Rizvi

23,586
22
91
133

Get return value from stored procedure using ExecuteSqlCommand (using Entity Framework)

1 Answers1