How to call stored procedure contains dynamic SQL with Entity Framework

Question

I'm using Entity Framework get data from SQL Server. I have a stored procedure that contains dynamic SQL to search data.

My SQL code looks like this:

create procedure SearchProduct
    (@ProductId int = null,
     @ProductName nvarchar(50) = null,
     @ProductCatId int = null)
as
    Set NoCount ON
    declare @SqlStr nvarchar(max)
    declare @ParamList nvarchar(2000)
begin
    set @SqlStr = 'select p.ProductId, p.ProductName, p.ProductCatName, p.From, p.To from Product p where (1=1)'
-- p.from , p. to is warranty period

    if @ProductName is not null
       set @SqlStr = 'and p.ProductName like ''%' +@ProductName + '%'' )'
    ......
    set @ParamList ='@ProductId,@ProductName,@ProductCat'

    execute sp_executesql @sqlStr, @ParamList, @ProductId, @ProductName, @ProductCat
end

I call this stored procedure SPDynamic1 from ASP.NET MVC like this:

var query = db.SPDynamic1(para1, para2, para3,...);

In here, query returns int. How to call the procedure to return result like

ProductId ProductName ProductCat
--------------------------------
1         prod001     5
2         prod001     2 
1         prod001     1

Yes, i have. create procedure SearchProduct(para1,para2,....)as set nocount on ... begin .... end — Mr.Ken
What are you asking? How to call dynamic SQL or how to get results from your procedure? — James Z
I want to call SP(contain Dynamic Sql) with data input and return data columns in table Product — Mr.Ken

Mr.Ken Mr.Ken · Accepted Answer · 2016-07-19T03:48:16

I found it. Entity Framework doesn't support Dynamic Sql.

How to call stored procedure contains dynamic SQL with Entity Framework

1 Answers