Embedded SQL vs ObjectScript SQL. What do you prefer and why?

Hi Community!

There are two general ways to execute arbitrary SQL  in serverside ObjectScript code: EmbeddedSQL and ObjectScript SQL a.k.a. Dynamic SQL.

E.g. if we want to get the value of the property of instance with a certain ID using SQL we can do:

&sql(SELECT Name INTO :name FROM Sample.Person WHERE ID=1)

write name

Same result with %SQL.Statement:

set rs=##class(%SQL.Statement).%ExecDirect(,"SELECT Name as name FROM Sample.Person where ID=1")
  do rs.%Next()
  write rs.name

Another typical task is to work through a set of records.

With Embedded SQL you do:

 &sql(DECLARE C1 CURSOR FOR 
       SELECT Name INTO :name 
       FROM Sample.Person
       WHERE Age > 60
       ORDER BY Name)
  &sql(OPEN C1)
      QUIT:(SQLCODE'=0)
  &sql(FETCH C1)
  WHILE (SQLCODE = 0) {
     WRITE name,!
    &sql(FETCH C1) }
  &sql(CLOSE C1)

And with SQL.Statement you have:

set rs=##class(%SQL.Statement).%ExecDirect(,"SELECT Name as name FROM Sample.Person WHERE Age > 60 ORDER BY Name  ")

 while rs.Next {

   write rs.Name,!
 }

My preference is %SQL.Statement or New Dynamic SQL.  Because it's more readable and not that wordy to work with resultsets.

What's your preference and why?

Good reads on this topic: Old/New Dynamic SQL Cheat Sheet, Class Queries in InterSystems Caché , Documentation on Executing SQL