CODEWITHSUNDEEP
asp.netentity-frameworkc#-4.0stored-procedures
Raju S Nair
Raju S Nair

Reputation: 333

Entity Framework shows error when called stored procedure

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

Upvotes: 1

Views: 2561

Answers (1)

Sean Airey
Sean Airey

Reputation: 6372

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)

Upvotes: 3

Related Questions