Stack Exchange
EF5 collecting data from stored procedure that returns a list or nothing
I have a stored procedure that looks like this:
CREATE PROCEDURE [dbo].[Foo]
(
  @ID INT,
  @PARAM1 NVARCHAR(10),
  @Error INT OUT
)
AS 
BEGIN
  IF (@ID < 0)
  BEGIN
    SET @Error = 1
    RETURN
  END

  IF (@ID > 100)
  BEGIN
    SET @Error = 2
    RETURN
  END

  SELECT Field1, Field2, Field3 FROM TABLE WHERE Param = @PARAM1
END

In EF5, I'm calling it like this
ObjectParameter error = new ObjectParameter("Error", typeof(global::System.Int32));
List<MyFoo> list = db.Foo(id, param1, error).ToList();

The code does not compile because "NOT ALL PATH RETURNS A VALUE" which makes sense since I have a couple of "RETURN" calls in the stored procedure.
QUESTION:
What's the right way to call this stored procedure from EF5 (cannot change the stored procedure)?
Thanks in advance

Since the stored procedure has return statments in the middle it is mor elike dynamic sql.I highly doubt EF with DB context will work with this.I think it is better to use the conventional ExecuteReader methods.This link migth be of help http://weblogs.asp.net/dwahlin/archive/2011/09/23/using-entity-framework-code-first-with-stored-procedures-that-have-output-parameters.aspx

Does the stored proc actually work if executed normally? I'm guessing that you might need a SET @Error = 0 newline RETURN at the end of that proc for it to work with EF...but I haven't dealt with them much, so I could be wrong.

@FizzBuzz the stored proc works that way yes ...

@KrishnanunniJeevan the blogger seemed to have a problem with the output param not being filled, my problem is as you stated the stored procedure not always returning a list. Maybe a fix is to replace the return with SELECT "" as field1, "" as field2 ... :--)

The other option is just to do it all in EF. Unless you're providing just a snippet of the actual stored proc, there's really no reason to even have it. The method doesn't process the query, it just checks the parameters and returns a query result. It would be much simpler to write a method that takes 2 parameters and returns a List, full or empty.

Remove the Error parameter and RETURNs from the SP and use RAISERROR to signal errors. Make sure that severity is greater than 10 so it would throw an SqlException, but less than 17 so it's not a system problem which is reported to DB admin:
CREATE PROCEDURE [dbo].[Foo]
(
  @ID INT,
  @PARAM1 NVARCHAR(10)
)
AS 
BEGIN
  IF (@ID < 0)
  BEGIN
    RAISERROR('ID is less than 0', 16, 0); 
  END

  IF (@ID > 100)
  BEGIN
    RAISERROR('ID is greater than 100', 16, 0); 
  END

  SELECT Field1, Field2, Field3 FROM TABLE WHERE Param = @PARAM1
END

