Entity Framework shows error when called stored procedure

Question

In my project EF calls a stored procedure which is shown below. It returns either 1 or scope identity.

On EF function imports, the stored procedure is listed with a return type of decimal.

When the stored procedure returns scope identity, everything is ok.

But when if condition of sp satisfies, ef throws error as

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

Pls help..

This is my stored procedure:

@VendorId int,
    @ueeareaCode varchar(3),
    @TuPrfxNo varchar(3),
    @jeeSfxNo varchar(4),
    @Tjode varchar(3),
    @uxNo varchar(3),
    @TyufxNo varchar(4),
    @Iyuy bit


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from

    SET NOCOUNT ON;

     IF EXISTS (Select dfen_id
                    from dbo.efe_phfedwn_eflwn
                    where
                    [yu] = @Tyuode and
                    [uy] = @TuyxNo and
                    [yuno] = @Tuo)
                    return 1
    ELSE
        Begin
            INSERT INTO dbo.yu 
                         ....................                       
            Select Scope_Identity()
    End
END

I'm not very experienced with EF but is it possible that because you're returning 1 or selecting the `Scope_Identity()` it's having difficulties? Could you try changing `Select Scope_Identity()` to `RETURN Scope_Identity()` and see if that makes a difference? — Sean Airey, May 30 '13 at 09:36
If that doesn't solve it (I don't have high hopes), it would be helpful for other people to see the code that EF is using to get the result of this SP, the Model code or whatever it is — Sean Airey, May 30 '13 at 09:39
no , I have tested using return .But now it shows the same error in both cases. Later, it was showing only when if condition satisfies. — Raju S Nair, May 30 '13 at 09:46
Ok so perhaps the solution is actually the other way around and you just use `SELECT 1` and `SELECT Scope_Identity()`? — Sean Airey, May 30 '13 at 09:50
when used select, else condition is ok, but on if condition, it cannot convert int32 to decimal . pls help.My Ef output is as decimal. — Raju S Nair, May 30 '13 at 09:55

score 3 · Accepted Answer · answered May 30 '13 at 10:10

3

The error tells us that EF is expecting a result set and when we use RETURN we don't get a result set. Your error means that the stored procedure is returning an integer but EF is expecting a decimal, so we just CAST the selected values to a decimal.

So modify the SQL so that we SELECT instead of RETURN, like so (not forgetting to use CAST):

 IF EXISTS (Select cntct_ctr_phn_ln_id
                from dbo.cntct_ctr_phn_ln
                where
                [toll_free_phn_area_cd] = @TollfreeareaCode and
                [toll_free_phn_prfx_no] = @TollfreePrfxNo and
                [toll_free_phn_sfx_no] = @TollfreeSfxNo)
                SELECT CAST(1 AS decimal)

Then also CAST the result of SCOPE_IDENTITY() to a decimal:

SELECT CAST(SCOPE_IDENTITY() AS decimal)

answered May 30 '13 at 10:10

Sean Airey

6,352
1
20
38

If Sp returns both decimal and integer, without doing cast at sp level , is any other way to handle at Ef level , so that Ef can accept both and can use or convert at the code level. – Raju S Nair May 31 '13 at 10:51
I honestly couldn't tell you. You may be able to override EF's default logic for building the object but I'm afraid I've not got that far in my understanding. – Sean Airey May 31 '13 at 18:54
But what if we NEED the return statement rather than a select statement to return out ID's in Sprocs? – Le-roy Staines May 10 '16 at 02:52
I'm not entirely sure, but [this question](http://stackoverflow.com/questions/14735477/get-return-value-from-stored-procedure) seems to suggest that you should use `EXEC` to select the return value of the stored procedure into an output parameter and then select the value of the parameter once the query has executed. – Sean Airey May 24 '16 at 13:21
@Le-royStaines Stored procedures provide data to the EF reader through a `SELECT` resultset, not a returned value. You don't _need_ a `RETURN` because EF isn't designed to access it. Only another stored procedure can use the returned value from a stored procedure. – Suncat2000 Jun 16 '23 at 23:47

Entity Framework shows error when called stored procedure

1 Answers1