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 `nvarchar`without any specific length, sql server will default this to `nvarchar(1)`. — M.Ali, Jul 09 '17 at 17:58
yeah i see i forgot that but it still give me the same error — omini data, Jul 09 '17 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, Jul 09 '17 at 18:19
i had tried to see if EF was an option but sadly is not due to this database structure — omini data, Jul 09 '17 at 18:22
Can we see the code for usp_iudCar? Not all of it but just the part that returns data. — CodingYoshi, Jul 09 '17 at 18:25
there is so much logic in that stored procedure and it returns alot of data so to make sense of it you might have to see the whole procedure (i don't even understand every thing it does) https://pastebin.com/TUV4a3jK — omini data, Jul 09 '17 at 18:31
The code you have posted to execute the stored procedure, what is the result it gives you on sql server? — CodingYoshi, Jul 09 '17 at 18:36
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, Jul 09 '17 at 18:49
arh isee that did the trick var update = context.Database.SqlQuery(sql).FirstOrDefault(); how do i check if it is had been updated then(get the return value etc) ? — omini data, Jul 09 '17 at 19:12

score 0 · Answer 1 · answered Jul 09 '17 at 18:49

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 09 '17 at 18:49

CodingYoshi

25,467
4
62
64

the problem with that is i do not have any optionss to select it as complex in the edmx – omini data Jul 09 '17 at 18:55
@ominidata why not? Why dont you have the option? – CodingYoshi Jul 09 '17 at 18:56
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 – omini data Jul 09 '17 at 19:03
@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. – CodingYoshi Jul 09 '17 at 19:08

execute stored procedure from SqlQuery data reader issue

1 Answers1