CODEWITHSUNDEEP
c#sql-serverentity-frameworkstored-proceduresazure-sql-database
Zain Rizvi
Zain Rizvi

Reputation: 24656

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

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?

Upvotes: 5

Views: 25675

Answers (1)

Zain Rizvi
Zain Rizvi

Reputation: 24656

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.

Upvotes: 8

Related Questions