Agent skill

embedded-sql

Use when writing SQL queries in ADVPL/TLPP using BeginSQL/EndSQL blocks, %table%, %notDel%, %xfilial%, %exp% macros, or when choosing between Embedded SQL and TCQuery string concatenation

View SKILL.md on GitHub Repository
Stars 116
Forks 22

Install this agent skill to your Project

npx add-skill https://github.com/thalysjuvenal/advpl-specialist/tree/main/skills/embedded-sql

SKILL.md

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

advpl
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():

advpl
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.

advpl
-- 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.

advpl
-- 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.

advpl
-- 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.

advpl
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.

advpl
-- 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:

advpl
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

advpl
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

advpl
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

advpl
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

advpl
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

advpl
#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:

advpl
// 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):

advpl
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):

advpl
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.

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

thalysjuvenal/advpl-specialist

advpl-to-tlpp-migration

Use when migrating ADVPL procedural code to TLPP object-oriented code, converting functions to classes, or modernizing legacy Protheus code

116 22
Explore
thalysjuvenal/advpl-specialist

protheus-reference

Use when looking up Protheus native functions, SX data dictionary tables, REST API endpoints, MV parameters, or TOTVS framework documentation

116 22
Explore
thalysjuvenal/advpl-specialist

documentation-patterns

Use when generating technical documentation for ADVPL/TLPP code - Protheus.doc headers, routine docs, API docs

116 22
Explore
thalysjuvenal/advpl-specialist

protheus-business

Use when consulting Protheus ERP business processes, module workflows, routines, integrations, or understanding how business operations work in TOTVS Protheus

116 22
Explore
thalysjuvenal/advpl-specialist

advpl-refactoring

Use when refactoring ADVPL/TLPP code - extract functions, simplify logic, remove dead code, improve naming

116 22
Explore
thalysjuvenal/advpl-specialist

code-explanation

Use when explaining ADVPL/TLPP code in plain language for developers and functional consultants

116 22
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results