sql-server - pass - table parameter stored procedure

Parámetro con valores de tabla en el procedimiento almacenado y Entity Framework 4.0

Question

Tengo un procedimiento almacenado en SQL Server 2008 llamado ''GetPrices'' con un parámetro de valor de tabla llamado ''StoreIDs''.

Este es el tipo que creé para este TVP:

CREATE TYPE integer_list_tbltype AS TABLE (n int)

Me gustaría llamar al SP desde mi Entity Framework. Pero cuando intento agregar el Procedimiento almacenado al EDM, aparece el siguiente error:

La función ''GetPrices'' tiene un parámetro ''StoreIDs'' en el parámetro índice 2 que tiene un tipo de datos ''tipo de tabla'' que no es compatible. La función fue excluida.

¿Hay alguna solución para esto? ¿Alguna idea?

Fabio

Answer 1

Como no puede usar un parámetro de tabla, intente pasar un valor CSV y haga que el procedimiento almacenado lo divida en filas.

Hay muchas maneras de dividir cadena en SQL Server. Este artículo cubre los PRO y CON de casi todos los métodos:

"Matrices y listas en SQL Server 2005 y más allá, cuando los parámetros de valores de tabla no lo cortan" por Erland Sommarskog

Necesita crear una función dividida. Así es como se puede usar una función dividida:

SELECT
*
FROM YourTable y
INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

Prefiero el método de la tabla de números para dividir una cadena en TSQL, pero existen numerosas maneras de dividir cadenas en SQL Server, consulte el enlace anterior, que explica los PRO y CON de cada uno.

Para que funcione el método de tabla de números, debe realizar esta configuración de tabla única, que creará una tabla de Numbers que contiene filas de 1 a 10.000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Una vez que la tabla Números está configurada, crea esta función dividida:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN
( ----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''''
);
GO 

Ahora puede dividir fácilmente una cadena CSV en una tabla y unirla o usarla como lo necesite:

CREATE PROCEDURE YourProcedure
(
@CSV_Param varchar(1000)
)
AS
--just an example of what you can do
UPDATE t
SET Col1=...
FROM dbo.FN_ListToTable('','',@CSV_Param) dt
INNER JOIN TBL_USERS t ON CAST(dt.value AS INT)=t.id
GO

Answer 2

Es posible que desee votar sobre esto en microsoft connect

Actualización: MS ya no usa Connect para funciones. Solo lo usan para informes de errores. Para votar por una función EF, debe ir al sitio EF User Voice .

Para votar sobre este tema específico en User Voice, vaya aquí .

Answer 3

Estoy de acuerdo en que pasar la picadura de CSV es la mejor solución en este caso. Me gustaría proponer una forma más simple de dividir la cadena csv, sin crear tablas y funciones, mediante el uso de 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);

Answer 4

Puede usar la propiedad ObjectContext.Connection para usar ADO.NET para crear y usar los parámetros con valores de tabla. Puede que esto no sea aceptable, pero si desea utilizar esta increíble característica de SQL Server 2008 y EF, parece ser que usted es el único elegido.

A continuación, puede optar por ampliar el contexto del objeto parcialmente generado con el método para ocuparse de todas las cosas de bajo nivel de ADO.NET. Me gusta esto:

public partial class FriendsOnBoardEntities : ObjectContext
{
public IList<int> GetPrices(int n)
{
// ''low-level'' ado.net stuff here.
// Use SqlParameters, SqlCommand and what not...
}
}