How to return values from a dynamic SQL Stored Procedure to the Entity Framework?

Question

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.

Is it just to have a simple demo case, or is your dynamic statement really as simple as described above? In this case, you would not need dynamic SQL. — Frank, Jul 02 '10 at 15:13
Well...this is just simple example..I am doing something complex....that's why using dynamic sql...i want to use it in entity framework..and get values.. — Vishal, Jul 02 '10 at 15:15
Can you fool the Entity Framework by temporarily putting in dummy code that returns a result set of the format you want? Then alter the procedure definition with the actual code. — Martin Smith, Jul 02 '10 at 15:44

score 16 · Accepted Answer · edited Feb 27 '15 at 00:17

16

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

edited Feb 27 '15 at 00:17

Brad

11,934
4
45
73

answered Jul 02 '10 at 15:21

Anon246

1,821
13
16

That is how I had it without scalar values...but entity framework didnt detect that it returned any columns.... – Vishal Jul 02 '10 at 15:25
Then perhaps you could post a little more info on why you need to do it dynamically. Usually there's a way to do this without dynamic SQL. – Anon246 Jul 02 '10 at 15:36
1

I have multiple where clauses...that get appended to the main select...when i pass according parameters...So using dynamic sql to build those where clauses...but really all i want to know is to return values explicitly so that ef4 recognizes it... – Vishal Jul 02 '10 at 15:42
In that case, the above edit should get you what you want. I just like to highlight best practices to prevent SQL injection. – Anon246 Jul 02 '10 at 15:59
Thanks it worked...will keep in mind the practices..:).. STACKOVERFLOW JUST ROCKS!!! – Vishal Jul 02 '10 at 16:51
@Strommy: thanx ever so much Strommy – Mohsen Aug 13 '11 at 09:20

Mohsen · Answer 2 · 2016-09-26T14:27:17.867

4

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.

edited Sep 26 '16 at 14:27

answered Aug 13 '11 at 09:56

Mohsen

4,000
8
42
73

Thanks of all them this was the quickest and easiest. Thanks again. – coolcake Nov 03 '11 at 02:26

score 1 · Answer 3 · answered Sep 25 '12 at 08:48

1

add following line at beginning of your SP

SET FMTONLY OFF

answered Sep 25 '12 at 08:48

Nitin Sawant

7,278
9
52
98

score 1 · Answer 4 · answered May 21 '14 at 08:26

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

Opochitsky Dimitry · Answer 5 · 2016-10-16T17:49:42.157

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.

score 0 · Answer 6 · answered Jul 02 '10 at 15:24

0

have you tried giving aliases to your last Select:

select @Salary as Salary, @UserName as UserName

answered Jul 02 '10 at 15:24

Moose

5,354
3
33
46

zeeshanhirani · Answer 7 · 2010-07-08T05:11:30.620

0

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.

edited Jul 08 '10 at 05:11

answered Jul 06 '10 at 14:30

zeeshanhirani

2,330
17
12

How to return values from a dynamic SQL Stored Procedure to the Entity Framework?

7 Answers7

Linked