callSQL ;
 ; compose an SQL Statement
 set query="SELECT something FROM TABLE_A JOIN TABLE_B ON ...........WHERE ...."  
 set rs=##class/%ResultSet.%NEW()
 set sc=rs.Prepare(query)
 if 'sc write $system.OBJ.DisplayError(sc)
 set sc=rs.Execute()
 if 'sc write $system.OBJ.DisplayError(sc)
 while rs.%Next() {
    ;consume the result set  row by row
}

OK yo can write this in .INT, in .MAC, in Methods .....

and SORRY:
IDX is still just 3 chars to me, as Athena is 6 chars. (or the Greek Godess of Wisdom)

OK. I was blocked by other activities. But this is my solution:
Assumption1:    Property JSON As %Stream.GlobalCharacter;
Assumption2:    You know the properties you want to index, as with normal tables 
The idea: A calculated property is mainly used for building indices 
The solution: the Stream needs to be presented as %DynamicObject to get the value.
 And here is it:

Property JSON As %Stream.GlobalCharacter;

Property FirstName As %String [ Calculated, SqlComputed ,
         SqlComputeCode = { set {*}=..GetDyn({ID},"FirstName") } ];

Property LastName As %String [ Calculated, SqlComputed ,
         SqlComputeCode = { set {*}=..GetDyn({ID},"LastName") } ];

Index fn on FirstName;
Index ln on LastName;

ClassMethod GetDyn(ID As %Integer, item = "") As %String
{
   set JSON=..%OpenId(ID).JSON
   do JSON.Rewind()
   set st=JSON.Read(3000000)
   set dyno={}.%FromJSON(st)
   set rep=$Property(dyno,item)
   quit rep
}

There is room to improve the speed of the method.    
Also saving your keys in individual properties during data load could be a valid approach.
The principle is always the same. %Stream --> %DynamicObject --> extract keys by name

A  rather personal view:
The development of customized tags was an attempt to hide COS from "TAG-SHUFFLERS"  
as we named back at the start of this century WebPage coders. Neither JS nor CSS was there yet,
But something more  "modern" than the previous VB5 and VB6 tools were required.

ISC was never a leader in WebPages rather a follower. Also with ZEN and MOJO.
And CSP was definitely never a buying argument for Caché or IRIS.
It was never a core business. Rather a necessary requirement, demanding quite some effort.
Today nobody is forced to drop existing development.  
It is there and it will persist, and it's not hidden, but it will not be pushed.

A personals note.
The first CSP Training for customers was written by my friend Salva (@Jose-Tomas Salvador) 
And I did the translation of this training to German and English.( ~ 2 decades back)

And as you might also have seen the version was 2021.2  which is not released yet.

Your reference explicitly mentions CSP files (with CSP markup language)
that are stored outside of Caché / IRIS. 

But this doesn't affect class-based CSP and the related server technology.  

as by reply of @Andreas Dieckow   on June 23, 2021
https://community.intersystems.com/post/advisory-discontinued-technologies-and-features#comment-159616

Caché Server Pages are fully supported on InterSystems IRIS and C/E.
 

??? you seem to insert the ID already  ???
set sql = " INSERT INTO Prenotazioni_CUP "_" (ID, cf)"_" VALUES (SEQTAB.NextVal, ?) 
??? not clear what you are looking for ???

What you experience is the effect of the Global Buffer Pool.
The rule is to overwrite the least used buffer if a new is required.
So the older the buffer the higher the chance to be overwritten and later reloaded.
Purging queries only affects code not data

Possible option:  increase  your buffer pool (double or triple size)
or try this approach: https://community.intersystems.com/post/global-buffer-questions
suggested by @Julius Kavay 

Utils.GetNumber('456')

is a static expression and not depending on rows so it is calculated only once.
 

do this changes:

ClassMethod GetSomeNumber(intInput As %Integer, id ) As %Integer [ SqlName = GetNumber, SqlProc ]

and your query

SELECT Utils.GetNumber('456',ID), ....

by adding ID you force a recalculation by a row dependency