Stored procedure not returning full data of view-CodePudding

I have a stored procedure in SQL Server that returns data from a view.

It basically does a SELECT statement like this:

SELECT * 
FROM MY_VIEW...

Using .NET 4.6, I'm calling it with ExecuteFunction.

I modified the view adding a new column and the stored procedure is not returning the new column's data. All my .NET models are already working expecting the new data, but it is just returning the default value.

If I do a SELECT * directly in SQL Server it does return the new data.

I have tried refreshing the view, recompiling the stored procedure with no success.

What am I missing? What am I doing wrong?

CodePudding user response：

If you add columns to a table, the stored procedure as parsed will not be updated to reflect that. You need EXEC sp_refreshsqlmodule to update it.

EXEC sp_refreshsqlmodule N'dbo.YourProcName';

Recompiling the query plan using sp_recompile will not help, as it does not update the parse tree, only the query plan created from it. See also Difference between sp_refreshview and sp_recompile

As far as Entity Framework is concerned, you also need to update the model so that it brings back that data. So you need to Update Model, see Entity Framework : How do you refresh the model when the db changes?