How to return values from a dynamic SQL Stored Pro

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

Try the below script this is working good.

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