存储过程中的表值参数和实体框架4.0 [英] Table-Valued Parameter in Stored Procedure and the Entity Framework 4.0
问题描述
这是我为此创建的类型,在SQL Server 2008中有一个称为GetPrices的存储过程,名为StoreIDs TVP:
CREATE TYPE integer_list_tbltype AS TABLE(n int)
我想从我的实体框架调用SP。但是,当我尝试将存储过程添加到EDM时,我会收到以下错误:
函数GetPrices在参数索引2处有一个参数StoreIDs具有不支持的数据类型表类型。该功能被排除。
有没有任何解决方法?任何想法?
Fabio
我同意,在这种情况下,CSV sting是最好的解决方案。
我想提出更简单的方法来拆分csv字符串,而不需要使用CTE创建表和函数:
声明@separator char(1);
set @separator =',';
;将baseCte作为
(select left(@ValueList,charindex(@separator,@ValueList) - 1))作为Value,
substring(@ValueList,charindex(@separator ,@ValueList)+ 1,len(@ValueList))
as rest
union all
select left(rest,charindex(@separator,rest) - 1)as Value,
substring(rest,charindex(@separator,rest)+ 1,len(rest))from baseCte
其中len(rest)> 1
)
从baseCte的值
选项(MAXRECURATION 0);
I have a stored procedure in SQL Server 2008 called 'GetPrices' with a Table-Valued Parameter called 'StoreIDs'.
This is the type i created for this TVP:
CREATE TYPE integer_list_tbltype AS TABLE (n int)
I would like to call the SP from my Entity Framework. But when I try to add the Stored Procedure to the EDM, i get the following error:
The function 'GetPrices' has a parameter 'StoreIDs' at parameter index 2 that has a data type 'table type' which is not supported. The function was excluded.
Is there any workaround this? Any thoughts?
Fabio
I agree that passing in a CSV sting is the best solution in this case. I would like to propose simpler way to split csv string, without creating tables and functions, by using CTE:
declare @separator char(1);
set @separator = ',';
;with baseCte as
(select left(@ValueList, charindex(@separator, @ValueList) - 1) as Value,
substring(@ValueList, charindex(@separator, @ValueList) + 1, len(@ValueList))
as rest
union all
select left(rest, charindex(@separator, rest) - 1) as Value,
substring(rest, charindex(@separator, rest) + 1, len(rest)) from baseCte
where len(rest) > 1
)
select Value from baseCte
OPTION (MAXRECURSION 0);
这篇关于存储过程中的表值参数和实体框架4.0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!