How to return values ​​from dynamic SQL stored procedure in Entity Framework?

Question

I have a stored procedure that executes some dynamic SQL. I want to use this stored procedure in entity infrastructure 4, but when I try to create a complex type, the procedure does not return columns. Is there any way to make it return my values ​​and get the entity infrastructure to receive them? Here is a very simplified example of what I want to do:

CREATE PROCEDURE sp_calculatesalary(@EmployeeId as int) begin declare dynsql as varachar(500) @dynsql='Select @Salary=Salary,@UserName=Username from employee where EmployeeId='+cast(@EmployeeId as varchar)+ '' exec(@dynsql) select @Salary, @UserName end 

But that does not work. Please help me. Basically, I want to use a stored procedure to execute dynamic SQL and return the values ​​to the entity infrastructure.

Answer 1

Perhaps you could consider parameterized SQL if you should make dynamic queries:

 CREATE PROCEDURE sp_calculatesalary(@EmployeeId as int) as begin declare @dynsql varchar(500) declare @params nvarchar(500) declare @salary money declare @username varchar(50) set @dynsql='Select @sal=Salary,@usernm=Username from employee where EmployeeId=@empID' set @params='@empID int, @sal money OUTPUT, @usernm varchar(50) OUTPUT' exec sp_executesql @dynsql, @params, @empID=@EmployeeID, @sal=@salary OUTPUT, @usernm = @username OUTPUT SELECT @salary, @username end 

Answer 2

try it

 CREATE PROCEDURE sp_calculatesalary(@EmployeeId as int) AS DECLARE @dynsql VARCHAR(500)=' Salary,Username FROM employee WHERE EmployeeId=@empID' EXEC sp_executesql @dynsql,'@empID INT',@empID=@EmployeeID SELECT 1 AS salary,2 AS username 

Trust me. It's enough.

Or you can simply create a complex type based on the result of the query, and then use the collection of the complex type as the result of the query.

Answer 3

add the following line at the beginning of your SP

 SET FMTONLY OFF 

Answer 4

Try the below script, this works well.

 BEGIN TRAN DECLARE @Result varchar(max),@Table varchar(max),@Column varchar(max) set @Column= 'CategoryName,CategoryID' set @Table='Category' set @Result= ' select ' + @Column + ' from '+@Table exec(@Result) ROLLBACK 

Answer 5

well, I think this is what you are looking for:

 create procedure sp_calculatesalary @employeeId int as declare @sql nvarchar(max); @sql='select salary, username from employee where employeeId=' + cast(@employeeId as nvarchar(10)); declare @t table (salary float, username varchar(50)); insert into @t exec(@sql); select salary, username from @t; return 

this will generate the public partial class sp_calculatesalary_Result in an entity-based DAL.

Answer 6

Have you tried giving aliases to the last choice:

 select @Salary as Salary, @UserName as UserName 

Answer 7

Well, if EF cannot recognize that your stored procedure should return, then create your complex type ahead of time. You can create a complex type by right-clicking anywhere on the model and add a complex type. Then, when you import the stored procedure, you can select a complex type from the drop-down list.