how to return dynamic stored procedures in entity framework

krishna kishore

11y9.4k.NET

Hi all,

i am using entity frame work for model in the data Access layer.

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_calculatesalarybegin(@EmployeeId as int)

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Personally, I would interested in knowing two things, before posting any fruitful reply:

1/ Do you REALLY have to use dynamic SQL? In other words, have you already explored other alternatives and have failed? I doubt that.

2/ After executing stored procedure in SSMS do you get the expected output?
Mukesh Nayak8y ago
Hi Krishna,
I think we can write the procedure like following:
CREATE PROCEDURE sp_calculatesalarybegin
(@EmployeeId AS INT)
AS
BEGIN
DECLARE @dynsql  NVARCHAR(500)
DECLARE @params NVARCHAR(500)
DECLARE @Employee_Salary MONEY
DECLARE @Employee_Name NVARCHAR(50)

SET @dynsql='SELECT @Salary=Employee_Salary,@Name=Employee_Name FROM  Employee WHERE Employee_ID=@Id'
SET @params='@Id INT, @Salary MONEY OUTPUT, @Name NVARCHAR(50) OUTPUT'
EXEC sp_executesql @dynsql, @params, @Id=@EmployeeID, @Salary=@Employee_Salary OUTPUT, @Name = @Employee_Name OUTPUT
SELECT @Employee_Salary, @Employee_Name
END
My table structure looks like following :
This will work correctly in Entity Framework
Regards,
Mukesh Nayak
chaitanya varma8y ago
Hello I am also looking for same answer.Can u share the answer to me if u found it.

Thanks in advance... :)
Prakash Lakshmanan11y ago
Refer this link :
http://www.devtoolshed.com/using-stored-procedures-entity-framework-scalar-return-values