我有一个存储过程,它执行一些动态sql.我想在实体框架4中使用此存储过程,但是当我尝试创建复杂类型时,过程不返回任何列.有什么办法可以强制它返回我的值并让实体框架接收它们吗？这是我想要做的简化示例：
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
但这不起作用.请帮帮我.基本上,我想使用存储过程来执行动态sql并将值返回到实体框架.
解决方法
如果必须进行动态查询,也许可以考虑参数化sql：
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
