Embedded SQL in ADVPL/TLPP

Overview

Embedded SQL allows writing SQL queries directly in ADVPL/TLPP code using BeginSQL ... EndSQL blocks with special macro expressions. It replaces error-prone string concatenation (cQuery += "SELECT...") with readable, type-safe, and maintainable SQL blocks.

When to Use

• Writing any SQL query in ADVPL/TLPP (prefer over string concatenation)
• Need to query with proper filial filtering, deletion handling, and table naming
• Building reports with complex SELECT, JOIN, GROUP BY
• Any situation where TCQuery with concatenated strings is currently used
• When readability and maintainability of SQL code matters

BeginSQL vs TCQuery (String Concatenation)

Aspect	BeginSQL (Modern)	TCQuery + Strings (Legacy)
Readability	SQL written naturally	SQL buried in string concat
Table names	%table:SE2% automatic	RetSqlName("SE2") manual
Filial filter	%xfilial:SE2% automatic	xFilial("SE2") manual
Deletion filter	%notDel% automatic	D_E_L_E_T_ = ' ' manual
Variable binding	%exp:cVar%	"'" + cVar + "'" (SQL injection risk)
Column types	column X as Date	TCSetField manual
Maintenance	Easy to read and modify	Hard to find errors in strings
SQL Injection	Protected via macros	Vulnerable if not careful

Always prefer BeginSQL for new code.

Core Syntax

BeginSQL Alias cAlias
    SELECT columns
    FROM %table:ALIAS% ALIAS
    WHERE ALIAS.%notDel%
    AND ALIAS.FIELD_FILIAL = %xfilial:ALIAS%
    AND ALIAS.FIELD = %exp:cVariable%
    ORDER BY %Order:ALIAS%
EndSQL

Important: The alias parameter can be a string literal or GetNextAlias():

Local cAlias := GetNextAlias()

BeginSQL Alias cAlias
    SELECT A1_COD, A1_LOJA, A1_NOME
    FROM %table:SA1% SA1
    WHERE SA1.%notDel%
    AND SA1.A1_FILIAL = %xfilial:SA1%
EndSQL

DbSelectArea(cAlias)
While !Eof()
    Conout(cAlias->A1_NOME)
    DbSkip()
EndDo
DbCloseArea()

Special Macro Expressions

%table:TABLE%

Resolves the physical table name with proper database schema/owner.

-- Input:
FROM %table:SA1% SA1

-- Expands to (example):
FROM SA1010 SA1
-- (where 010 = company code, depends on environment)

%xfilial:TABLE%

Returns the current branch (filial) value for the table.

-- Input:
AND SA1.A1_FILIAL = %xfilial:SA1%

-- Expands to:
AND SA1.A1_FILIAL = '01'
-- (or '' if table is not branch-filtered)

%notDel%

Filters out logically deleted records.

-- Input:
WHERE SA1.%notDel%

-- Expands to:
WHERE SA1.D_E_L_E_T_ <> '*'

Always include this. Protheus uses logical deletion, not physical.

%exp:EXPRESSION%

Binds ADVPL variables, expressions, or function results into the SQL.

Local cCodCli := "000001"
Local cLoja   := "01"
Local dDataIni := CtoD("01/01/2026")

BeginSQL Alias cAlias
    SELECT E2_PREFIXO, E2_NUM, E2_VALOR, E2_EMISSAO
    FROM %table:SE2% SE2
    WHERE SE2.%notDel%
    AND SE2.E2_FILIAL  = %xfilial:SE2%
    AND SE2.E2_FORNECE = %exp:cCodCli%
    AND SE2.E2_LOJA    = %exp:cLoja%
    AND SE2.E2_EMISSAO >= %exp:DtoS(dDataIni)%
EndSQL

Note: %exp:% handles quoting automatically for character fields. For dates, use DtoS() to convert.

%Order:TABLE%

Returns the primary key ordering for the table.

-- Input:
ORDER BY %Order:SE2%

-- Expands to the primary key columns of SE2

Column Type Declaration

Declare result column types to avoid manual TCSetField calls:

BeginSQL Alias cAlias
    column E2_EMISSAO as Date
    column E2_VENCTO  as Date
    column E2_VALOR   as Numeric(16,2)

    SELECT E2_PREFIXO, E2_NUM, E2_EMISSAO, E2_VENCTO, E2_VALOR
    FROM %table:SE2% SE2
    WHERE SE2.%notDel%
    AND SE2.E2_FILIAL = %xfilial:SE2%
EndSQL

// Columns are automatically typed - no TCSetField needed
DbSelectArea(cAlias)
While !Eof()
    Local dEmissao := (cAlias)->E2_EMISSAO   // Already Date type
    Local nValor   := (cAlias)->E2_VALOR      // Already Numeric
    DbSkip()
EndDo

Without column declaration, all fields return as Character and need manual conversion.

JOIN Patterns

INNER JOIN

BeginSQL Alias cAlias
    SELECT SE2.E2_PREFIXO, SE2.E2_NUM, SE2.E2_VALOR,
           SA2.A2_NOME, SA2.A2_CGC
    FROM %table:SE2% SE2
    INNER JOIN %table:SA2% SA2
        ON SE2.E2_FORNECE = SA2.A2_COD
        AND SE2.E2_LOJA   = SA2.A2_LOJA
        AND SA2.A2_FILIAL = %xfilial:SA2%
        AND SA2.%notDel%
    WHERE SE2.%notDel%
    AND SE2.E2_FILIAL = %xfilial:SE2%
    AND SE2.E2_EMISSAO >= %exp:DtoS(dDataIni)%
    ORDER BY SE2.E2_EMISSAO
EndSQL

LEFT JOIN

BeginSQL Alias cAlias
    SELECT SC5.C5_NUM, SC5.C5_CLIENTE, SC5.C5_LOJACLI,
           SA1.A1_NOME
    FROM %table:SC5% SC5
    LEFT JOIN %table:SA1% SA1
        ON SC5.C5_CLIENTE = SA1.A1_COD
        AND SC5.C5_LOJACLI = SA1.A1_LOJA
        AND SA1.A1_FILIAL = %xfilial:SA1%
        AND SA1.%notDel%
    WHERE SC5.%notDel%
    AND SC5.C5_FILIAL = %xfilial:SC5%
EndSQL

Best practice: Always use explicit JOIN syntax, not comma-separated FROM.

Aggregation Patterns

SUM / COUNT / AVG

BeginSQL Alias cAlias
    column TOTAL as Numeric(16,2)
    column QTD   as Numeric(10,0)

    SELECT SE2.E2_FORNECE, SE2.E2_LOJA,
           SUM(SE2.E2_VALOR) AS TOTAL,
           COUNT(*) AS QTD
    FROM %table:SE2% SE2
    WHERE SE2.%notDel%
    AND SE2.E2_FILIAL = %xfilial:SE2%
    AND SE2.E2_EMISSAO BETWEEN %exp:DtoS(dDataIni)% AND %exp:DtoS(dDataFim)%
    GROUP BY SE2.E2_FORNECE, SE2.E2_LOJA
    HAVING SUM(SE2.E2_VALOR) > 0
    ORDER BY TOTAL DESC
EndSQL

Subquery

BeginSQL Alias cAlias
    SELECT SA1.A1_COD, SA1.A1_NOME
    FROM %table:SA1% SA1
    WHERE SA1.%notDel%
    AND SA1.A1_FILIAL = %xfilial:SA1%
    AND SA1.A1_COD IN (
        SELECT SC5.C5_CLIENTE
        FROM %table:SC5% SC5
        WHERE SC5.%notDel%
        AND SC5.C5_FILIAL = %xfilial:SC5%
        AND SC5.C5_EMISSAO >= %exp:DtoS(dDataIni)%
    )
EndSQL

Complete Pattern: Query with Cursor Iteration

#Include "TOTVS.CH"
#Include "TopConn.ch"

User Function QueryEx()
    Local cAlias := GetNextAlias()
    Local aArea  := GetArea()
    Local aResult := {}
    Local dDataIni := Date() - 30

    BeginSQL Alias cAlias
        column E2_EMISSAO as Date
        column E2_VENCTO  as Date
        column E2_VALOR   as Numeric(16,2)
        column SALDO      as Numeric(16,2)

        SELECT SE2.E2_PREFIXO, SE2.E2_NUM, SE2.E2_PARCELA,
               SE2.E2_FORNECE, SE2.E2_LOJA,
               SE2.E2_EMISSAO, SE2.E2_VENCTO,
               SE2.E2_VALOR,
               (SE2.E2_VALOR - SE2.E2_PAGO) AS SALDO,
               SA2.A2_NOME
        FROM %table:SE2% SE2
        INNER JOIN %table:SA2% SA2
            ON SE2.E2_FORNECE = SA2.A2_COD
            AND SE2.E2_LOJA   = SA2.A2_LOJA
            AND SA2.A2_FILIAL = %xfilial:SA2%
            AND SA2.%notDel%
        WHERE SE2.%notDel%
        AND SE2.E2_FILIAL = %xfilial:SE2%
        AND SE2.E2_EMISSAO >= %exp:DtoS(dDataIni)%
        AND (SE2.E2_VALOR - SE2.E2_PAGO) > 0
        ORDER BY SE2.E2_VENCTO
    EndSQL

    DbSelectArea(cAlias)
    While !(cAlias)->(Eof())
        aAdd(aResult, {;
            (cAlias)->E2_PREFIXO,;
            (cAlias)->E2_NUM,;
            (cAlias)->E2_VALOR,;
            (cAlias)->SALDO,;
            (cAlias)->A2_NOME;
        })
        (cAlias)->(DbSkip())
    EndDo
    (cAlias)->(DbCloseArea())

    RestArea(aArea)
Return aResult

Restrictions and Gotchas

Rule	Why
Do NOT start a line with * inside BeginSQL	ADVPL pre-compiler treats * at line start as comment
Always include %notDel%	Protheus uses logical deletion, never rely on physical
Always include %xfilial% or filial filter	Multi-branch environments will return wrong data
Use GetNextAlias() for the alias	Avoids alias name conflicts with open work areas
Always DbCloseArea() after done	Prevents work area leaks (limited number of aliases)
Do NOT use SELECT *	Specify columns explicitly for performance and clarity
Date values must use DtoS()	Database stores dates as YYYYMMDD strings
column declarations go BEFORE the SELECT	They define result types, not query columns

DML Operations

For INSERT, UPDATE, DELETE use TCSqlExec instead of BeginSQL:

// INSERT
Local cSql := "INSERT INTO " + RetSqlName("ZZ1") + " "
cSql += "(ZZ1_FILIAL, ZZ1_CODIGO, ZZ1_DESCRI, D_E_L_E_T_, R_E_C_N_O_) "
cSql += "VALUES ('" + xFilial("ZZ1") + "', '001', 'Teste', ' ', " + cValToChar(GetSxeNum("ZZ1","ZZ1_CODIGO")) + ")"
nRet := TCSqlExec(cSql)

// UPDATE
cSql := "UPDATE " + RetSqlName("ZZ1") + " SET "
cSql += "ZZ1_DESCRI = 'Novo Valor' "
cSql += "WHERE ZZ1_FILIAL = '" + xFilial("ZZ1") + "' "
cSql += "AND ZZ1_CODIGO = '001' "
cSql += "AND D_E_L_E_T_ = ' '"
nRet := TCSqlExec(cSql)

If nRet < 0
    Conout("SQL Error: " + TCSqlError())
EndIf

BeginSQL is for SELECT only. Use TCSqlExec for DML (INSERT/UPDATE/DELETE).

Performance Tips

1. Use column declarations to avoid post-query type conversions
2. Use JOINs instead of subqueries when possible
3. Filter early with WHERE, not after reading all rows in ADVPL
4. Use NOLOCK hint for read-only queries (SQL Server): FROM %table:SA1% SA1 WITH(NOLOCK)
5. Use TOP N when you only need limited results
6. Close aliases immediately after use to free work areas
7. Avoid loops with BeginSQL inside - build one query that returns all needed data

Migration from TCQuery to BeginSQL

Before (string concatenation):

Local cQuery := ""
cQuery += "SELECT A1_COD, A1_NOME "
cQuery += "FROM " + RetSqlName("SA1") + " SA1 "
cQuery += "WHERE SA1.D_E_L_E_T_ = ' ' "
cQuery += "AND A1_FILIAL = '" + xFilial("SA1") + "' "
cQuery += "AND A1_TIPO = '" + cTipo + "'"
TCQuery cQuery New Alias "QRY_CLI"

After (Embedded SQL):

Local cAlias := GetNextAlias()

BeginSQL Alias cAlias
    SELECT A1_COD, A1_NOME
    FROM %table:SA1% SA1
    WHERE SA1.%notDel%
    AND SA1.A1_FILIAL = %xfilial:SA1%
    AND SA1.A1_TIPO = %exp:cTipo%
EndSQL

Benefits: No manual RetSqlName, no D_E_L_E_T_, no xFilial(), no string quoting, no SQL injection risk.