如何将值从动态SQL存储过程返回到实体框架? [英] How to return values from a dynamic SQL Stored Procedure to the Entity Framework?
本文介绍了如何将值从动态SQL存储过程返回到实体框架?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
CREATE PROCEDURE sp_calculatesalary(@EmployeeId as int)
begin
declare dynsql as varachar(500)
@ dynsql ='Select @ Salary = Salary,@ UserName =用户名从employee
其中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 @saryary money
declare @username varchar(50)
set @ dynsql ='Select @ sal =工资,@ usernm = 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
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.
解决方案
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
这篇关于如何将值从动态SQL存储过程返回到实体框架?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文