Calling Stored Procedure In Entity Framework

Question

I have the following part in the end of a SQL Server stored procedure:

if(@someValue < 0)
  begin
    SELECT @resultIsSuccess = 0
    Return @resultIsSuccess
  end
else
  begin
    SELECT @resultIsSuccess = 1
    Return @resultIsSuccess
  end

where @resultIsSuccess is of type bit.

So, basically I am returning a bool to indicate if the procedure yielded the intended result.

On the EF side, I configured the Function Import's return type as boolean.

When I call:

bool isSuccess = context.MyFunctionImport(arg1, arg2).FirstOrDefault().Value;

I get the following exception:

The data reader returned by the store data provider does not have enough columns for the query requested.

What is the problem here?

Why not try `SELECT 0` or `SELECT 1` instead of adding an extra line and declaring a variable? It could be because you aren't actually "reading" anything but you are outputting to an return variable. — interesting-name-here, Aug 15 '17 at 19:17
You should never use the return value of a stored procedure to return data. Use an output parameter or a resultset. — David Browne - Microsoft, Aug 15 '17 at 20:54

score 2 · Answer 1 · answered Aug 15 '17 at 19:25

If you are returning a value, you need a return parameter. Therefore you aren't using a datareader to get your value. You aren't selecting anything therefore you would need to have access to that return parameter.

Instead select your values to populate the datareader since you are probably getting an error when you try to read values from a noninstantiated datareader through EF.

if(@someValue < 0)
    SELECT 0
else
    SELECT 1

I'm unsure if return values are supported, reference, so you may be trying to do the impossible unless fixed in a newer version.

Sean Lange · Answer 2 · 2017-08-15T20:23:19.817

0

You have to keep in mind that you are NOT returning a bit datatype from your procedure. The return datatype of a stored procedure is int.

You could greatly simplify your code to a single line return statement.

return case when @someValue < 0 then 0 else 1 end

Then in your code you would need to parse the 0 or 1 to a boolean.

--EDIT-- Since you are looking for the first value you will need to use a select statement in your procedure. Something like this.

Select isSuccess  = case when @someValue < 0 then 0 else 1 end

edited Aug 15 '17 at 20:23

answered Aug 15 '17 at 19:26

Sean Lange

33,028
3
25
40

I tried this but it calling the function always return 1. Even though I changed the return values to be numbers such as 5 or 6, it returned 1. – John L. Aug 15 '17 at 20:17
How are you checking your return? RETURN_VALUE. Honestly you should probably be using an OUTPUT parameter. – Sean Lange Aug 15 '17 at 20:18
I am doing int isSuccess = context.MyFunctionImport(arg1, arg2); – John L. Aug 15 '17 at 20:20
Ah....then you are getting the values from the first select statement in your procedure...NOT the return value. – Sean Lange Aug 15 '17 at 20:22

Calling Stored Procedure In Entity Framework

2 Answers2