Uso de parámetros grandes para el procedimiento almacenado de SQL con DAO

Como muchos de ustedes ya saben, el equipo de SQL Server ha anunciado el desuso de OLEDB para el motor de base de datos de SQL Server (Lea: no podemos usar ADO porque ADO usa OLEDB). Además, SQL Azure no es oficialmente compatible con ADO, aunque uno puede salirse con la suya utilizando SQL Server Native Client. Sin embargo, el controlador 13.1 ODBC viene con una serie de características que no estarán disponibles en el SQL Server Native Client, y es posible que haya más.

El resultado final: necesitamos trabajar con DAO puro. Ya hay varios elementos de voz del usuario tocando el tema de Access / ODBC o Access / SQL Server … por ejemplo:

Conector de datos SQL Server
Mejor integración con SQL Server
Mejor integración con SQL Azure
Haga que Access pueda manejar más tipos de datos tal como se usan comúnmente en las bases de datos del servidor
Haga de Access un mejor cliente ODBC

(Si no ha votado o visitado access.uservoice.com, vaya allí y vote si desea que el equipo de Access implemente su función favorita)

Pero incluso si Microsoft mejora el DAO en la próxima versión, todavía tenemos que lidiar con las aplicaciones existentes de nuestros clientes. Consideramos usar ODBC sobre el proveedor OLEDB (MSDASQL) pero sentimos que era similar a montar en un caballo moribundo. Podría funcionar, pero podría morir un poco más adelante.

En su mayor parte, una consulta de paso hará lo que tenemos que hacer y es fácil combinar una función para imitar la funcionalidad de ADO utilizando una consulta de transferencia DAO. Pero hay una brecha importante que no se soluciona fácilmente: grandes parámetros para los procedimientos almacenados. Como escribí anteriormente, en algún momento usamos el parámetro XML como una forma de pasar gran cantidad de datos, que es mucho más rápido que hacer que Access realmente inserte todos los datos uno por uno. Sin embargo, una consulta DAO está limitada a unos 64K caracteres para el comando SQL y en la práctica puede ser aún menor. Necesitábamos una forma de pasar parámetros que pudieran tener más de 64K caracteres, por lo que tuvimos que pensar en una solución alternativa.

Ingrese la tabla tblExecuteStoredProcedure

El enfoque que elegimos fue usar una tabla porque cuando usamos controladores ODBC más nuevos o SQL Server Native Client, DAO puede manejar fácilmente una gran cantidad de texto (también conocido como Memo) al insertarlo directamente en la tabla. Por lo tanto, para ejecutar un parámetro XML grande, escribiremos el procedimiento a ejecutar y su parámetro en la tabla, luego dejaremos que el disparador lo recoja. Aquí está el script de creación de la tabla:

CREATE TABLE dbo.tblExecuteStoredProcedure (
	ExecuteID int NOT NULL IDENTITY
		CONSTRAINT PK_tblExecuteStoredProcedure PRIMARY KEY CLUSTERED,
	ProcedureSchema sysname NOT NULL
		CONSTRAINT DF_tblExecuteStoredProcedure DEFAULT 'dbo',
	ProcedureName sysname NOT NULL,
	Parameter1 nvarchar(MAX) NULL,
	Parameter2 nvarchar(MAX) NULL,
	Parameter3 nvarchar(MAX) NULL,
	Parameter4 nvarchar(MAX) NULL,
	Parameter5 nvarchar(MAX) NULL,
	Parameter6 nvarchar(MAX) NULL,
	Parameter7 nvarchar(MAX) NULL,
	Parameter8 nvarchar(MAX) NULL,
	Parameter9 nvarchar(MAX) NULL,
	Parameter10 nvarchar(MAX) NULL,
	RV rowversion NOT NULL
);

Por supuesto, en realidad no tenemos la intención de usar esto como una tabla real. También establecemos arbitrariamente 10 parámetros a pesar de que un procedimiento almacenado puede tener muchos más. Sin embargo, en nuestra experiencia, es bastante raro tener mucho más de 10, especialmente cuando se trata de parámetros XML. Por sí solo, la tabla no sería muy útil. Necesitamos un disparador:

CREATE TRIGGER dbo.tblExecuteStoredProcedureAfterInsert
ON dbo.tblExecuteStoredProcedure AFTER INSERT AS
BEGIN
	--Throw if multiple inserts were performed
	IF 1 < (
		SELECT COUNT(*)
		FROM inserted
	)
	BEGIN
		ROLLBACK TRANSACTION;
		THROW 50000, N'Cannot perform multiple-row inserts on the table `tblExecuteStoredProcedure`.', 1;
		RETURN;
	END;
 
	--Process only single record which should be the last inserted
	DECLARE @ProcedureSchema sysname,
			@ProcedureName sysname,
			@FullyQualifiedProcedureName nvarchar(MAX),
			@Parameter1 nvarchar(MAX),
			@Parameter2 nvarchar(MAX),
			@Parameter3 nvarchar(MAX),
			@Parameter4 nvarchar(MAX),
			@Parameter5 nvarchar(MAX),
			@Parameter6 nvarchar(MAX),
			@Parameter7 nvarchar(MAX),
			@Parameter8 nvarchar(MAX),
			@Parameter9 nvarchar(MAX),
			@Parameter10 nvarchar(MAX),
			@Params nvarchar(MAX),
			@ParamCount int,
			@ParamList nvarchar(MAX),
			@Sql nvarchar(MAX);
 
	SELECT 
		@ProcedureSchema = p.ProcedureSchema,
		@ProcedureName = p.ProcedureName,
		@FullyQualifiedProcedureName = CONCAT(QUOTENAME(p.ProcedureSchema), N'.', QUOTENAME(p.ProcedureName)),
		@Parameter1 = p.Parameter1,
		@Parameter2 = p.Parameter2
	FROM inserted AS p
	WHERE p.RV = (
		SELECT MAX(x.RV)
		FROM inserted AS x
	);
 
	SET @Params = STUFF((
		SELECT
			CONCAT(
				N',',
				p.name,
				N' = ',
				p.name
			)
		FROM sys.parameters AS p
		INNER JOIN sys.types AS t
			ON p.user_type_id = t.user_type_id
		WHERE p.object_id = OBJECT_ID(@FullyQualifiedProcedureName)
		FOR XML PATH(N'')
	), 1, 1, N'');
 
	SET @ParamList = STUFF((
		SELECT 
			CONCAT(
				N',',
				p.name,
				N' ',
				t.name,
				CASE 
					WHEN t.name LIKE N'%char%' OR t.name LIKE '%binary%' 
					THEN CONCAT(N'(', IIF(p.max_length = -1, N'MAX', CAST(p.max_length AS nvarchar(11))), N')')
					WHEN t.name = 'decimal' OR t.name = 'numeric'
					THEN CONCAT(N'(', p.precision, N',', p.scale, N')')
					ELSE N''
				END
			)
		FROM sys.parameters AS p
		INNER JOIN sys.types AS t
			ON p.user_type_id = t.user_type_id
		WHERE p.object_id = OBJECT_ID(@FullyQualifiedProcedureName)
		FOR XML PATH(N'')
	), 1, 1, N'');
 
	SET @ParamCount = (
		SELECT COUNT(*)
		FROM sys.parameters AS p
		WHERE p.object_id = OBJECT_ID(@FullyQualifiedProcedureName)
	);
 
	SET @ParamList += ((
		SELECT 
			CONCAT(N',', p.ParameterName, N' nvarchar(1)')
		FROM (VALUES 
			(1, N'@Parameter1'),
			(2, N'@Parameter2'),
			(3, N'@Parameter3'),
			(4, N'@Parameter4'),
			(5, N'@Parameter5'),
			(6, N'@Parameter6'),
			(7, N'@Parameter7'),
			(8, N'@Parameter8'),
			(9, N'@Parameter9'),
			(10, N'@Parameter10')
		) AS p(ParameterID, ParameterName)
		WHERE p.ParameterID > @ParamCount
		FOR XML PATH(N'')
	));
 
	SET @Sql = CONCAT(N'EXEC ', @FullyQualifiedProcedureName, N' ', @Params, N';');
 
	--Prevent any result sets from being returned from a trigger (which is deprecated)
	--If a stored procedure returns any, the trigger will end in an error
	EXECUTE sys.sp_executesql @Sql, @ParamList, @Parameter1, @Parameter2, @Parameter3, @Parameter4, @Parameter5, @Parameter6, @Parameter7, @Parameter8, @Parameter9, @Parameter10
	WITH RESULT SETS NONE;
	
	DELETE FROM dbo.tblExecuteStoredProcedure
	WHERE EXISTS (
		SELECT NULL
		FROM inserted
		WHERE inserted.ExecuteID = tblExecuteStoredProcedure.ExecuteID
	);
END;

Un absoluto bocado, este Trigger. Básicamente, se necesita una sola inserción, luego se descubre cómo convertir los parámetros de su nvarchar (MAX) como se define en la tabla tblExecuteStoredProcedure al tipo real requerido por el procedimiento almacenado. Se utilizan conversiones implícitas, y dado que está envuelto en un sys.sp_executesql funciona bien para una variedad de tipos de datos siempre que los valores de los parámetros sean válidos. Tenga en cuenta que requerimos que el procedimiento almacenado NO devuelva ningún conjunto de resultados. Microsoft permite que los triggers devuelvan conjuntos de resultados, pero como se señaló, no es estándar y ha quedado en desuso. Entonces, para evitar problemas con futuras versiones de SQL Server, bloqueamos esa posibilidad. Finalmente, limpiamos la tabla, por lo que siempre está vacía. Después de todo, estamos abusando de la tabla; No estamos almacenando ningún dato.

Public Sub ExecuteWithLargeParameters( _
    ProcedureSchema As String, _
    ProcedureName As String, _
    ParamArray Parameters() _
)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
   
    Dim i As Long
    Dim l As Long
    Dim u As Long
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM tblExecuteStoredProcedure;", dbOpenDynaset, dbAppendOnly Or dbSeeChanges)
    
    rs.AddNew
    rs.Fields("ProcedureSchema").Value = ProcedureSchema
    rs.Fields("ProcedureName").Value = ProcedureName
    
    l = LBound(Parameters)
    u = UBound(Parameters)
    For i = l To u
        rs.Fields("Parameter" & i).Value = Parameters(i)
    Next
    
    rs.Update
End Sub

	
ExecuteWithLargeParameters "dbo", "uspMyStoredProcedure", dteStartDate, dteEndDate, strSomeBigXMLDocument