How To: SQL Server JSON parser

Ed eccomi ancora qui a pensare a qualcosa come sempre fuori dagli schemi... ammetto questa volta la cosa è stata decisamente complessa. Ma ha dato un risultato davvero inatteso.

Per una necessità mi serve passare un array ad una stored procedure, tuttavia non mi è possibile determinare a priori i campi, presenti nell'array... diciamo anche che il fantastico mondo dei varchar, mi ha menato una zappa sul malleolo.

Quindi ho pensato a json ( anche se questa versione è un po stravolto ) ma molto sta nella convenzione che utilizzerò per scriverlo.

/* 
    Questo mi rappresenta il parametro iniziale
        indicativamente 
        sono la 
        - definizione del parametro 
        - il suo valore
*/
DECLARE @jMasterText AS VARCHAR(max)
SET @jMasterText = '{{d:01/01/2010,v:-,t:5007.79}|
                    {d:01/01/2011,v:5080.84,t:5132.98}|
                    {d:01/01/2012,v:5180.84,t:5432.98}}'

/* 
    Per prima cosa è necessario
        spezzare i campi sapendo che 
        [a:x,b:y,c:z]
        dove 
        a è il nome del 1 campo
        x è il valore del 1 campo
        i : separano i campi da i valori
        i | separano i recodr
*/

DECLARE @iTime as int 

SET @iTime = 0

WHILE (charindex('|',@jMasterText)>0 )
BEGIN

    DECLARE @jText AS VARCHAR(max)

    SET @jText = ltrim(rtrim(Substring(@jMasterText,1,Charindex('|',@jMasterText)-1)))        
    SET @jMasterText = Substring(@jMasterText,Charindex('|',@jMasterText)+1,len(@jMasterText))

    DECLARE @jTempText AS VARCHAR(MAX)
    DECLARE @jField AS VARCHAR(MAX)
    DECLARE @jValue AS VARCHAR(MAX)

    DECLARE @jValueList AS VARCHAR(MAX)
    DECLARE @jFieldListD AS VARCHAR(MAX)
    DECLARE @jFieldList AS VARCHAR(MAX)

    SET @jTempText = @jText

    SET @jTempText = replace(@jTempText,'{','')
    SET @jTempText = replace(@jTempText,'}','')

    SET @jFieldListD = ''
    SET @jFieldList = ''
    SET @jValueList = ''

    /* ciclo per l'estazione degli elementi */
    
    WHILE (charindex(',',@jTempText)>0 )
    BEGIN
        SET @jField = ltrim(rtrim(Substring(@jTempText,1,Charindex(',',@jTempText)-1)))        
        SET @jTempText = Substring(@jTempText,Charindex(',',@jTempText)+1,len(@jTempText))
        
        SET @jValue = ltrim(rtrim(Substring(@jField,Charindex(':',@jField)+1,len(@jField))))
        SET @jField = ltrim(rtrim(Substring(@jField,1,Charindex(':',@jField)-1)))
            
        SET @jFieldListD = @jFieldListD + ',' + @jField +' varchar (max) '
        SET @jFieldList = @jFieldList + ',' +  @jField 
        SET @jValueList = @jValueList + ',*' + @jValue +'*'
    END

    /*
        rimane l'ultimo campo da elaborare
    */
    
    SET @jField = ltrim(rtrim(Substring(@jTempText,1,len(@jTempText))))

    SET @jValue = ltrim(rtrim(Substring(@jField,Charindex(':',@jField)+1,len(@jField))))
    SET @jField = ltrim(rtrim(Substring(@jField,1,Charindex(':',@jField)-1)))    

    SET @jFieldListD = @jFieldListD + ',' + @jField +' varchar (max) '
    SET @jFieldList = @jFieldList + ',' + @jField
    SET @jValueList = @jValueList + ',*' + @jValue +'*'

    /*
        in questo punto abbiamo l'elenco 
        completo dei campi 
    */
    
    SET @jFieldListD =Substring(@jFieldListD,2,len(@jFieldListD))
    SET @jFieldList =Substring(@jFieldList,2,len(@jFieldList))
    SET @jValueList =Substring(@jValueList,2,len(@jValueList))

    SET @jValueList =replace(@jValueList,'*','''')

    /* 
        Questa è la definizione della sintassi 
            sql che dovrò creare per poi effettuare 
            la selezione
    */
    
    DECLARE @jResultCreate AS VARCHAR(max)
    DECLARE @jResultInsert AS VARCHAR(max)

    IF @iTime = 0 
    BEGIN    
        SET @jResultCreate = 'CREATE TABLE ##tbJResult ('+ @jFieldListD +')'
        EXEC(@jResultCreate)
        SET @iTime = 1
    END
    

    SET @jResultInsert = 'INSERT INTO ##tbJResult (' + @jFieldList+ ') VALUES (' + @jValueList+ ')'
    EXEC(@jResultInsert)

END

/*
    rimane l'ultimo elemento da elaborare
*/
SET @jText = ltrim(rtrim(Substring(@jMasterText,1,len(@jMasterText))))        

SET @jTempText = @jText

SET @jTempText = replace(@jTempText,'{','')
SET @jTempText = replace(@jTempText,'}','')

SET @jFieldListD = ''
SET @jFieldList = ''
SET @jValueList = ''

WHILE (charindex(',',@jTempText)>0 )
BEGIN
    SET @jField = ltrim(rtrim(Substring(@jTempText,1,Charindex(',',@jTempText)-1)))        
    SET @jTempText = Substring(@jTempText,Charindex(',',@jTempText)+1,len(@jTempText))
    
    SET @jValue = ltrim(rtrim(Substring(@jField,Charindex(':',@jField)+1,len(@jField))))
    SET @jField = ltrim(rtrim(Substring(@jField,1,Charindex(':',@jField)-1)))
        
    SET @jFieldListD = @jFieldListD + ',' + @jField +' varchar (max) '
    SET @jFieldList = @jFieldList + ',' +  @jField 
    SET @jValueList = @jValueList + ',*' + @jValue +'*'
END

SET @jField = ltrim(rtrim(Substring(@jTempText,1,len(@jTempText))))

SET @jValue = ltrim(rtrim(Substring(@jField,Charindex(':',@jField)+1,len(@jField))))
SET @jField = ltrim(rtrim(Substring(@jField,1,Charindex(':',@jField)-1)))    

SET @jFieldListD = @jFieldListD + ',' + @jField +' varchar (max) '
SET @jFieldList = @jFieldList + ',' + @jField
SET @jValueList = @jValueList + ',*' + @jValue +'*'

SET @jFieldListD =Substring(@jFieldListD,2,len(@jFieldListD))
SET @jFieldList =Substring(@jFieldList,2,len(@jFieldList))
SET @jValueList =Substring(@jValueList,2,len(@jValueList))

SET @jValueList =replace(@jValueList,'*','''')

SET @jResultInsert = 'INSERT INTO ##tbJResult (' + @jFieldList+ ') VALUES (' + @jValueList+ ')'
EXEC(@jResultInsert)

SELECT * FROM ##tbJResult
DROP Table ##tbJResult

Qui c'e' tutta la parte di codice che a sua volta è inclusa in una stored molto più considerevole.

Spero che tutto ciò possa essere utile anche per altri .. !