execute stored procedure from SqlQuery data reader issue

Question

so i am trying to execute my stored procedure as folowing

            String sql =
        "SET NOCOUNT ON; " +
        "DECLARE @id INT, @itemnumber nvarchar(20); " +
        "SELECT @id= '" + car.ID + "'" +
        "EXEC [file].[usp_iudCar] " +
        "@p_ID=@id OUTPUT," +
        "@p_Location= '" + location + "', " +
        "@p_ItemNumber=@itemnumber OUTPUT, " +
        "@p_DoerTicket= '" + userToken + "' " +
        "SELECT @id AS id, @itemnumber AS itemNumber; ";

            QueryParamCollection queryParams = new QueryParamCollection();
            queryParams.AddInt32Param(QueryParamName.CarID, (object)car.ID);
            queryParams.AddStringParam(QueryParamName.Location, (object)location);
            queryParams.AddStringParam(QueryParamName.DoerTicket, (object)userToken);

            var update = context.Database.SqlQuery<Int32>(sql).FirstOrDefault<Int32>();

            return update;

similar to when i doing it from query on the server as

   DECLARE @return_value int,
            @p_ID int,
            @p_ItemNumber nvarchar(20)

    SELECT  @p_ID = 1783999

    EXEC    @return_value = [file].[usp_iudCar]
            @p_ID = @p_ID OUTPUT,
            @p_ItemNumber = @p_ItemNumber OUTPUT,
            @p_Location = N'test',
            @p_DoerTicket = N'0x0100000057065fc3a91f34c3f1f9cad41e2f5889bac6a68d3eab408dddc1cd54e57ce240565294f481f4f248bca4fb772d38fd737a6448dcbbfd9d58'

    SELECT  @p_ID as N'@p_ID',
            @p_ItemNumber as N'@p_ItemNumber'

    SELECT  'Return Value' = @return_value

    GO

result set

but i gets an execption

"ExceptionMessage": "The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.",

so how do i fix that or what is the correct way to do this?

one thing that I can see obviously wrong is you haven't defined the length of @itemnumber variable , you have used data type nvarcharwithout any specific length, sql server will default this to nvarchar(1). — M.Ali
– M.Ali, Commented Jul 9, 2017 at 17:58
yeah i see i forgot that but it still give me the same error — omini data
– omini data, Commented Jul 9, 2017 at 18:01
I strongly recommend to not use stored procedures as they aren't very easily testable. I suggest using EF instead if you have the option of doing that. — Maritim
– Maritim, Commented Jul 9, 2017 at 18:19
i had tried to see if EF was an option but sadly is not due to this database structure — omini data
– omini data, Commented Jul 9, 2017 at 18:22
Your call wants one column in the result set, convertible to an Int32. You are giving it a result set with two columns. This has precisely nothing to do with the fact that you're calling a stored procedure. Simpler repro: SELECT 1 AS A, 2 AS B. — Jeroen Mostert
– Jeroen Mostert, Commented Jul 9, 2017 at 18:49

CodingYoshi · Accepted Answer · 2017-07-09 18:49:06Z

0

This error:

"ExceptionMessage": "The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.",

means that your stored procedure returns more than just one field. This is what you are telling EF:

var update = context.Database.SqlQuery<Int32>(sql).FirstOrDefault<Int32>();

that the query returns a single field but clearly that is not the case or you would not get that error.

Also that is not how to call a stored procedure using EF database first approach. Please refer this question and accepted answer for how to do what you want the proper way.

answered Jul 9, 2017 at 18:49

CodingYoshi

27.3k7 gold badges68 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

omini data Over a year ago

the problem with that is i do not have any optionss to select it as complex in the edmx

CodingYoshi Over a year ago

@ominidata why not? Why dont you have the option?

omini data Over a year ago

we had been 2 people that had been trying to figure out why and our conclussion is that the way this database is Structured on

CodingYoshi Over a year ago

@ominidata ok, how you do it is upto you. You can create a type yourself that agrees with the result returned from the stored procedure and use that instead of Int32. Your stored procedure must always return the exact type in all cases or it will break again.

Collectives™ on Stack Overflow

execute stored procedure from SqlQuery data reader issue

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related