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?
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.