Solución:
En la presentación sobre Optimización de aplicaciones de Microsoft SQL Server 2008 usando parámetros de valores de tabla, XML y MERGE de Michael Rys, dice. (a las 32:52)
Tenga en cuenta que en SQL Server 2008, los parámetros con valores de tabla son de solo lectura. Pero como se dará cuenta, en realidad le pedimos que escriba LECTURA. Entonces, eso significa que en algún momento en el futuro, tal vez si dices por favor, por favor, por favor, con la frecuencia suficiente, podríamos ser capaces de hacer que se puedan escribir también en algún momento. Pero por el momento son de solo lectura.
Aquí está el elemento de conexión que debe usar para agregar su “por favor”. Relajar la restricción de que los parámetros de la tabla deben ser de solo lectura cuando los SP se llaman entre sí.
Srini Acharya hizo un comentario sobre el elemento de conexión.
Permitir que los parámetros con valores de tabla se lean / escriban implica bastante trabajo en el lado del motor SQL, así como en los protocolos del cliente. Debido a limitaciones de tiempo / recursos, así como a otras prioridades, no podremos realizar este trabajo como parte de la versión de SQL Server 2008. Sin embargo, hemos investigado este problema y lo tenemos firmemente en nuestro radar para abordarlo como parte de la próxima versión de SQL Server.
Los parámetros con valores de tabla tienen las siguientes restricciones (fuente MSDN):
- SQL Server no mantiene estadísticas en columnas de parámetros con valores de tabla.
- Los parámetros con valores de tabla deben pasarse como entrada READONLY
parámetros a las rutinas Transact-SQL. No puede realizar DML
operaciones como ACTUALIZAR, ELIMINAR o INSERTAR en una tabla con valores
parámetro en el cuerpo de una rutina. - No puede utilizar un parámetro con valores de tabla como destino de una instrucción SELECT INTO o INSERT EXEC. Un parámetro con valores de tabla puede estar en la cláusula FROM de SELECT INTO o en la cadena INSERT EXEC o en el procedimiento almacenado.
hay pocas opciones para superar esta restricción, una es
CREATE TYPE RTableType AS TABLE(id INT, NAME VARCHAR )
go
CREATE PROCEDURE Rproc @Rtable RTABLETYPE READONLY,
@id INT
AS
BEGIN
SELECT *
FROM @Rtable
WHERE ID = @id
END
go
DECLARE @Rtable RTABLETYPE
DECLARE @Otable RTABLETYPE
INSERT INTO @Rtable
VALUES (1,'a'),
(2,'b')
INSERT @Otable
EXEC Rproc
@Rtable,
2
SELECT *
FROM @Otable
a través de esto puede obtener los valores de la tabla
Con respecto a (énfasis agregado):
Así que alguien, por favor, dame una buena razón. por qué Los TVP fueron diseñados para ser parámetros de entrada de solo lectura.
Acabo de publicar una respuesta más detallada a esto en DBA.StackExchange aquí:
READONLY parámetros y restricciones de TVP
Pero el resumen es así:
De acuerdo con esta publicación de blog (TSQL Basics II – Parameter Passing Semantics), un objetivo de diseño de los parámetros de SALIDA del procedimiento almacenado es que simplemente imiten el comportamiento “por referencia” cuando el procedimiento almacenado se completa con éxito! Pero cuando hay un error que hace que el procedimiento almacenado se anule, cualquier cambio realizado en cualquier parámetro de SALIDA no reflejarse en el valor actual de esas variables cuando el control regrese al proceso de llamada.
Pero cuando se introdujeron los TVP, los implementaron como verdaderamente pasantes por referencia, ya que continuar con el modelo “por valor”, en el que se realiza una copia para garantizar que los cambios se pierdan si el procedimiento almacenado no se completa correctamente, no lo haría. ser eficiente / escalable, especialmente si se transmiten muchos datos a través de TVP.
Por lo tanto, solo hay una instancia de la variable de tabla que es el TVP, y cualquier cambio realizado dentro de cualquier procedimiento almacenado (si no se limita a ser READONLY) se conservaría inmediatamente y permanecería, incluso si el procedimiento almacenado encontrara un error. Esto viola el objetivo de diseño indicado al principio de este resumen. Y, no hay opción para vincular de alguna manera los cambios realizados en un TVP a una transacción (incluso algo manejado automáticamente, detrás de escena) ya que las variables de la tabla no están limitadas por transacciones.
Por lo tanto, marcarlos como READONLY es la única forma (por el momento) de mantener el objetivo de diseño de los parámetros del procedimiento almacenado de manera que no reflejen los cambios realizados dentro del procedimiento almacenado a menos que: el parámetro se declare como OUTPUT y el procedimiento almacenado se completa correctamente.