type - Parámetros con valores de tabla para los procedimientos CLR en SQL Server 2008: ¿posible?

table type sql server

Question

Esta página de SQL Server 2008 BOL , habla sobre los procedimientos almacenados de CLR y tiene una sección denominada "Parámetros con valores de tabla", que habla sobre cómo pueden ser ventajosos. Eso es genial. Me encantaría usar TVP en mis procedimientos de CLR, pero desafortunadamente esta parece ser la única referencia en el universo a tal posibilidad, y la sección no describe cuál sería la sintaxis (ni tampoco la información adicional) enlazado al final del párrafo)

Claro, puedo encontrar fácilmente descripciones de cómo usar los TVP de los procedimientos T-SQL o cómo hacer los procedimientos CLR en general. ¿Pero escribiendo un proc de CLR que toma un TVP? Nada. Todo esto es muy poco habitual, ya que la transferencia de datos de varias filas a un proceso almacenado es un problema popular.

Esto me lleva a preguntarme si la presencia de la sección en esa página es un error. Alguien, por favor, dime que no lo es y apúntame a más información / ejemplos.

[EDITAR]

Estaba a punto de publicar esto en uno de los foros de MS cuando me encontré con esto, que parece ser el último clavo en el ataúd . Parece que no se puede hacer.

Answer 1

La solución es serializar sus datos tabulares en una cadena con formato Json y luego pasar la cadena a su proceso CLR. Dentro de su clr proc o function usted analizaría el json a un objeto IEnumerable, lista u tabular. Luego puede trabajar con los datos como lo haría con cualquier otro tipo de datos tabulares.

He escrito algunas utilidades capaces de serializar cualquier tabla de sql en una cadena con formato Json. Me encantaría compartirlos con cualquier persona que proporcione su dirección de correo electrónico. Phil Factor ha escrito un buen analizador T-SQL Json al que llamó parseJson. He adaptado su solución al clr que realiza mucho más rápido. Ambos aceptan una cadena con formato Json y producen una tabla a partir de la cadena. También tengo una variedad de utilidades Json que empleo con T-SQL y CLR capaces de serializar, analizar, insertar, eliminar y actualizar cadenas con formato Json almacenadas en columnas SQL.

Answer 2

Puede usar una tabla temporal creada y completada antes de llamar al procedimiento y leer la tabla dentro del procedimiento clr.

Answer 3

Si bien parece que pasar las tablas directamente a los procedimientos de CLR es actualmente imposible, obtuve un resultado, aunque sub óptimo, por:

• definiendo una tabla TSQL con valor UDT FooTable
• definiendo una función TSQL que toma FooTable como parámetro y devuelve XML usando FOR XML EXPLICIT
• pasar el XML resultante a la función / procedimiento CLR en lugar de a la propia tabla

No ideal, pero se acerca un poco más.

Answer 4

Si usa C # (a diferencia de VB, que carece de iteradores personalizados), puede escribir código ADO.NET para invocar a ExecuteNonQuery () y ejecutar un procedimiento almacenado con un parámetro SqlDbType.Structured (es decir, un TVP).

La colección pasada como valor del TVP debe implementar IEnumerable<SqlDataRecord> . Cada vez que se ejecuta este rendimiento de IEnumerable, una "fila" de SqlDataRecord se canaliza al parámetro "tabla".

Vea este artículo para más detalles.

Answer 5

Puedo encontrar lot more references . Sin embargo, estos son todos para pasar parámetros con valores de tabla a procedimientos TSQL, por lo que es de poca utilidad.

Sin embargo, he llegado a la conclusión de que es imposible. Primero, está la lista de asignaciones entre los tipos CLR y SQL. Para los tipos de tablas no hay asignación, por lo que lo siguiente no funciona, por ejemplo:

[SqlProcedure]
public static void StoredProcedure(DataTable tvp, out int sum)
{
    return 42;
}

y entonces

CREATE TYPE MyTableType AS TABLE 
(
    Id INT NOT NULL PRIMARY KEY,
    [Count] INT NOT NULL
)
GO
CREATE ASSEMBLY ClrTest FROM ''<somePath>''
GO
CREATE PROCEDURE ClrTest
AS EXTERNAL NAME ClrTest.StoredProcedures.StoredProcedure
GO

Independientemente del tipo que intente ( DataTable , DbDataReader , IEnumerable ), la llamada CREATE PROCEDURE sigue generando un error 6552: CREATE PROCEDURE para "ClrTest" falló porque los tipos T-SQL y CLR para el parámetro "@tvp" no coinciden.

En segundo lugar, la documentación en la página a la que se vinculó dice: Un tipo de tabla definida por el usuario no se puede pasar como un parámetro con valores de tabla a un procedimiento almacenado administrado o una función que se ejecuta en el proceso de SQL Server.

Parece que no puedo encontrar en ningún lugar cómo crear un tipo de tabla definido por el usuario en C #, pero esto también parece ser un callejón sin salida.

Tal vez usted pueda hacer su pregunta en algún lugar de un foro de Microsoft. Todavía es extraño que mencionen parámetros con valores de tabla en la página de información de CLR pero nunca explican cómo implementar esto. Si encuentras alguna solución, me gustaría saber.