Entity Framework executing stored procedure does not return values

Question

Does anyone have any suggestions as to why the variable works returns 743 rows, but the variable notworks returns none?

As far as I can see they are doing the same thing.

I cannot publish the stored procedure, but I don't think that is the issue here as I am able to get a result set from the first variable.

I tried using a SQL trace - which I am new to - and could not see anything that helped me solve this.

var TretSpefParameter = new SqlParameter("TretSpef", "101");
var SexParameter = new SqlParameter("Sex", 1);
var SDiagParameter = new SqlParameter("SDiag", "*");

var works = await Context.Database.SqlQuery<sp_RefData_Predictive>("sp_RefData_Predictive @TretSpef = '101', @Sex = 1, @SDiag = '*'").ToListAsync();

var notWorks = await Context.Database.SqlQuery<sp_RefData_Predictive>("sp_RefData_Predictive @TretSpef, @Sex, @SDiag", TretSpefParameter, SexParameter, SDiagParameter).ToListAsync();

Post the stored procedure declaration. You are using named parameters in the first call, but positional parameters in the second one, which could cause a parameter assignment mismatch. — David Browne - Microsoft
– David Browne - Microsoft, Commented Jul 30, 2019 at 12:52
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Jul 30, 2019 at 13:03

Alex · Accepted Answer · 2019-07-30 13:18:14Z

1

I had to change:

@TretSpef, @Sex, @SDiag

to

@TretSpef=@TretSpef, @Sex=@Sex, @SDiag=@SDiag


var notWorks = await Context.Database.SqlQuery<sp_RefData_Predictive>("sp_RefData_Predictive @TretSpef=@TretSpef, @Sex=@Sex, @SDiag=@SDiag", TretSpefParameter, SexParameter, SDiagParameter).ToListAsync();

answered Jul 30, 2019 at 13:18

Alex

4,05810 gold badges51 silver badges100 bronze badges

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

Collectives™ on Stack Overflow

Entity Framework executing stored procedure does not return values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related