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:
CR开发者_如何学CEATE 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.
Perhaps you could consider parameterized SQL, if you must do 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
try this
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
Believe me. That is enough.
Or you can simply create a complex type based on your query result , and then use collection of the complex type as your query result.
add following line at beginning of your SP
SET FMTONLY OFF
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
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 a public partial class sp_calculatesalary_Result in entity framework based DAL.
have you tried giving aliases to your last Select:
select @Salary as Salary, @UserName as UserName
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.
精彩评论