Stored column retrieval information returns nothing?

Question

I use an entity structure with a stored procedure in which I dynamically generate a query and execute this query. The saved proc request looks like this:

Begin DECLARE @Query nvarchar(MAX) SET @Query = 'SELECT e.id, e.name, e.add, e.phno from employee' EXEC sp_executesql @Query End 

In the sql code above, you can see that I am executing the '@Query' variable, and this variable value can be dynamically changed.

I can add the saved proc to my edmx file. and then I go to the browser model and say Add function import and try to Get column information not to show anything. but when I execute the stored proc on the server, it returns all columns with values. Why am I not getting column information in the model browser?

Answer 1

Try adding SET NOCOUNT ON after BEGIN ...., which suppresses messages that may cause "confusion"

Answer 2

The stored procedure is not executed in the model’s browser in order to then collect information about the column from its result — it tries to grab column information from the definition of the base procedure using sys tables.

This procedure, since it is dynamic, will not have a basic definition and therefore will not be imported into EDMX like this.

Answer 3

Temporarily change the saved proc to

 SELECT TOP 1 e.id, e.name, e.add, e.phno from employee /* ... rest of your proc commented out */ 

Then add it to EF and create a function import (it will see the columns).

Then modify the process before you receive it.