如何将值从动态SQL存储过程返回到实体框架？ [英] How to return values from a dynamic SQL Stored Procedure to the Entity Framework?

问题描述

我有一个存储过程，它执行一些动态SQL。我想在实体框架4中使用这个Stored Procedure，但是当我尝试创建一个复杂的类型时，该过程不返回列。有什么办法可以强制它返回我的价值观，并让实体框架接收它们吗？这是一个很简单的例子，我想做的是：

  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屋！