Invoke T-SQL function from Genexus

Question

I defined a function with parameters in T-SQL in SQL Server 2012. Now I want to invoke that function from Genexus passing the referred parameters. Is that possible?

DBRet allows only tables and views to be conected from the KB. Is any other way to do that work?

Function

CREATE FUNCTION [dbo].[mayor_rubro_codigo](@rubroId char(12),@codigoId char(12), @fechaIni date,@fechaFin date)

RETURNS TABLE 
AS
RETURN 
(
    select fecha,rubro,codigo,refe,iif(dbcr='D',impmn+impme,0) DEBE,iif(dbcr='H',impmn+impme,0) HABER  from mayor where rubro=@rubroId and codigo=@codigoId and fecha>=@fechaIni and fecha<=@fechaFin and impmn+impme>0
)

Query

select * from mayor_rubro_codigo('101101','1001',{d '2018-01-01'},{d '2019-12-31'})

Parameters

Rubro : 101101 Código: 1001 Rango de fechas: 01/01/2018 al 31/12/2019

Answer 1

You cannot invoke a function like that from GeneXus. You could call a stored procedure, but it can't return a recordset, only scalar values.

I think your best option here is to use Dynamic Transactions. A Dynamic Transaction is like any other ordinary transaction but it has an associated Data Provider, and ends up generating a view in SQL that can have parameters.

Say you create the Dynamic Transaction, then you can use the for each command like you would normally do with any other Transaction:

for each mayor_rubro_codigo
    where rubroId = '101101'
    where codigoId = '1001'
    where fechaIni = #2018-01-01#
    where fechaFin = #2019-12-31#

    // your processing code here
endfor