How to return values from a dynamic SQL Stored Procedure to the Entity Framework?

Question

I have a Stored Procedure which executes some dynamic SQL. I want to use this Stored Procedure in entity framework 4, but when I try to create a complex type the procedure returns no columns. Is there any way I can force it to return my values and get the entity framework to receive them? Here is a much-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 this does not work. Please help me out. Basically, I want to use a Stored Procedure to execute dynamic SQL and return the values to the entity framework.

Answer 1

Perhaps you could consider parameterized SQL, if you must do dynamic queries:

CREATE PROCEDURE sp_calculatesalary(@EmployeeId as int)   
begin   
declare @dynsql varchar(500)   
declare @params nvarchar(500)
declare @salary money
declare @username varchar(50)
@dynsql='Select @sal=Salary,@usernm=Username from employee where EmployeeId=@empID'   
@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

Answer 2

have you tried giving aliases to your last Select:

select @Salary as Salary, @UserName as UserName

Answer 3

Well, if EF cannot recognize what your stored procedure must return then create your complex type ahead of time. You can create a complex type by right clicking any where on the model and and add a complex type. Next when you import your stored procedure, you can choose your complex type from the dropdown.