SSRS: Como usar una consulta SQL Server openquery parametrizada

Parametrizar una consulta en la que usamos un servidor vinculado de SQL Server mediante openquery puede ser necesario en diferentes escenarios. Por ejemplo podemos necesitarlo para cargar una tabla intermedia con los datos de venta que sacamos de MySql a través de un servidor vinculado en SQLServer. También en un informe de SQL Server Reporting Services.

[[ad]] Vamos con un ejemplo donde consultamos una tabla de Mysql directamente:

SELECT *
FROM Ventas.CABECERAS
WHERE date_format(FECHA, '%Y-%m-%d') >= '2012-12-01'

Esta misma consulta, lanzada desde SQL Server usando openquery y un servidor vinculado llamado 'MY' para acceder sería:

SELECT *
FROM openquery(MY, 'SELECT * FROM Ventas.CABECERAS
WHERE date_format(FECHA, ''%Y-%m-%d'') >= '''2012-12-01''')

** En otro este post explico como crear el servidor vinculado

[[ad]] Si quisiéramos parametrizarla, se nos ocurre primero añadirle un parámetro fecha, o segundo, construir la cadena de texto con las fechas usando variables tipo varchar (usare esta segunda forma para luego concatenar el resultado con el de otra query). Pero nada de esto funciona, ni aun pasando la consulta que pasa por openquery por variable:

DECLARE @fecha varchar;
SET @fecha = '2012-12-01'

SELECT *
FROM openquery(MY, 'SELECT * FROM Ventas.CABECERAS
WHERE date_format(FECHA, ''%Y-%m-%d'') >= ''' + @fecha + '''')
GO

DECLARE @fecha VARCHAR
DECLARE @consulta VARCHAR(8000)
SET @fecha = '2012-12-01'
SET @consulta = 'SELECT * FROM Ventas.CABECERAS WHERE date_format(FECHA, ''%Y-%m-%d'') >= ''' + @fecha + ''''

SELECT * FROM openquery(MY, @consulta)
GO

La única manera de que funcione y lo interprete es meter la consulta y el openquery en la variable @sql para llamarlo con EXEC como si fuera un procedimiento almacenado:

DECLARE @fecha VARCHAR
DECLARE @consulta VARCHAR(8000)
SET @fecha = '2012-12-01'
SET @consulta = 'SELECT * FROM openquery(MY, ''SELECT * FROM Ventas.CABECERAS WHERE date_format(FECHA, ''%Y-%m-%d'') >= ''' + @fecha + ''')'

exec (@consulta)
GO

Un ejemplo como este puede suponer un problema si queremos llamarlo desde un informe de SSRS. Report builder no nos dejara declarar variables que realmente no son parámetros a fijar del dataset. Para poder usarlo como origen de datos de un informe lo que debemos hacer es meterlo dentro de un procedimiento almacenado para evitar el problema con la variable @sql (que es de uso interno) y permitir usar la fecha como filtro:

/* --- Creación del procedure en Sqlserver --- */

CREATE PROCEDURE [dbo].[procVentasMy]
@fecha datetime

AS

SET NOCOUNT ON
BEGIN

DECLARE @consulta VARCHAR(8000)

SET @consulta = 'SELECT * FROM openquery(MY, ''SELECT * FROM Ventas.CABECERAS WHERE date_format(FECHA, ''%Y-%m-%d'') >= ''' + @fecha + ''')'

exec (@consulta)

END

/* --- Uso del mismo en Report Builder ---*/

EXEC [dbo].[procVentasMy] @fechaMinima

[[ad]] Este ejemplo va relacionado con este otro post sobre SQL Server donde se explica cómo concatenar en SQL Server resultados de dos origenes de datos distintos mediante openquery. En ese caso también puede ser necesario parametrizar la consulta y podemos hacerlo exactamente igual.