How to use a variable inside a function in SQL Server

Question

In brief, I am trying to use a variable inside a SQL Server function that increases its own value by one on each round inside the While loop.

I am taking an xml text into a variable and trying to retrieve a value inside an attribute, but it has more than one similar elements, so I need to retrieve it one by one.

The problem is in the last part of this code:

DECLARE
        @XMLTI NVARCHAR(MAX),
        @now INT = 1,
        @pagos VARCHAR(50),
        @Trx33 INT = 1,
        @NumOperacionT VARCHAR(19),
        @FechaPagoT VARCHAR(19),
        @FormaDePagoT VARCHAR(50),
        @MonedaT VARCHAR(80),
        @TipoCambioT DECIMAL(22, 2) = 1.00,
        @CtaBeneficiarioT VARCHAR(50),
        @RfcEmisorCtaBenT VARCHAR(13),
        @MontoT DECIMAL(22, 2),
        @XML XML

SET @XMLTI = (SELECT xml FROM emi_trx33_complementos_r WHERE id_complemento = @Trx33 )

SET @XMLTI = REPLACE( @XMLTI, '<cfdi:', '<' )   
SET @XMLTI = REPLACE( @XMLTI, '</cfdi:', '</' ) 
SET @XMLTI = REPLACE( @XMLTI, '<tfd:', '<' )    
SET @XMLTI = REPLACE( @XMLTI, '<pago10:', '<' )
SET @XMLTI = REPLACE( @XMLTI, '</pago10:', '</' )   
SET @XMLTI = REPLACE( @XMLTI, '<nomina12:', '<' )   
SET @XMLTI = REPLACE( @XMLTI, '</nomina12:', '</' )

DROP TABLE IF EXISTS #Pagos

CREATE TABLE #Pagos 
(
    Id INT NOT NULL IDENTITY PRIMARY KEY ,
    NumOperacion VARCHAR(19),
    FechaPago VARCHAR(19),
    FormaDePago VARCHAR(50),
    Moneda VARCHAR(80),
    TipoCambio DECIMAL(22, 2),
    CtaBeneficiario VARCHAR(50),
    RfcEmisorCtaBen VARCHAR(13),
    Monto DECIMAL(22, 2)
)

SET @pagos = @XMLTI
SET @XML = @XMLTI

SET @pagos = CONVERT(VARCHAR(MAX), (SELECT @XML.query('count(/Comprobante/Complemento/Pagos/Pago)')))
SET @NumOperacionT =  CONVERT(VARCHAR(MAX), (SELECT @XML.value('data(/Comprobante/Complemento/Pagos/Pago/@NumOperacion)[@MyVariable]', 'VARCHAR(30)')))

--Pagos =3
--now =1
WHILE @now < @pagos
BEGIN
    SET @NumOperacionT = CONVERT(VARCHAR(MAX), (SELECT @XML.value('data(/Comprobante/Complemento/Pagos/Pago/@NumOperacion)[@now]', 'VARCHAR(30)')))

    INSERT INTO X table @NumOperacionT 

    @now = @now + 1
END

But I get the following error

ERROR SET @NumOperacionT = convert(varchar(max),(select @XML.value('data(/Comprobante/Complemento/Pagos/Pago/@NumOperacion)[@MyVariable]','varchar(30)')))

select @NumOperacionT as Operacion

[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]XQuery [value()]: A node or set of nodes is required for / (2374)

Tim Leaf · Answer 1 · 2019-09-13T23:48:43.377

You are feeding a string into the value function, so if you want to insert the values of variables into the data directory inside this string, you need to pull the variables outside the string and concatenate, like this:

SET @NumOperacionT = convert(varchar(max),(select @XML.value('data(/Comprobante/Complemento/Pagos/Pago/@NumOperacion)[' + @now + ']','varchar(30)')))

However, this will probably also throw an error, since if it is error checking on the contents of the string, you will need to use dynamic SQL instead. For example:

declare @sql varchar(4000) 
set @sql = 'SET @NumOperacionT = convert(varchar(max),(select @XML.value(''data(/Comprobante/Complemento/Pagos/Pago/@NumOperacion)[' + @now + ']'',''varchar(30)'')))'
exec(@sql)

Though honestly, since you're using local variables, this most likely won't work either, since the dynamic SQL won't know about the variables, because it's on a different process.

You could get around this by using actual table data to manage your loop, but it's generally not recommended, and can cause a lot of unexpected issues, especially with multi-threading scenarios.

There should be a cleaner way to write this without using a loop. I recommend trying to find an alternative you can use to retrieve the data you need in actual table format, instead of using variables. Hard to say without knowing the data, but you might try some of the methods outlined in this thread for example:

Getting multiple records from xml column with value() in SQL Server

How to use a variable inside a function in SQL Server

1 Answers1