BeginSql AliasComandoRequired
Starts the block and names the result cursor.
FOUNDATIONPublished
Embedded SQL in AdvPL
BeginSql Alias cAlias ... EndSqlWrite readable SQL directly in AdvPL source code with precompiler substitutions and declarative type conversion.
Overview
Embedded SQL replaces manual string concatenation with a BeginSql/EndSql block. Literal SQL is preserved while percent-delimited expressions resolve Protheus tables, branches, values, deletion filters and index orders. The cursor opens in the supplied alias and ChangeQuery() runs by default.
Syntax
BeginSql Alias cAlias ... EndSqlParameters
column ... as Date | Logical | NumericDeclaraçãoOptional
Declares date, logical or numeric result conversion.
%table:ALIAS%SubstituiçãoOptional
Resolves the physical table through RetSqlName().
%xfilial:ALIAS%SubstituiçãoOptional
Inserts the branch value for the table.
%notDel%SubstituiçãoOptional
Generates the logical-deletion filter.
%exp:expressão%SubstituiçãoOptional
Evaluates a character, date, numeric or logical AdvPL value.
%Order:ALIAS,nOrdem%SubstituiçãoOptional
Converts an AdvPL index order through SqlOrder().
%temp-table:cNome%SubstituiçãoOptional
Inserts a temporary-table name stored in a variable.
%noparser%ModificadorOptional
Skips ChangeQuery(); use only for an intentional DBMS-specific case.
EndSqlComandoRequired
Ends the block and opens the cursor.
Customer query with Protheus conversions
// See the Portuguese example; only user-facing text should be localized.Expected result
The precompiler resolves the table, branch, value, deletion filter and order.
Declarative conversion and diagnostics
Local cAlias := GetNextAlias()
Local aInfo := {}
BeginSql Alias cAlias
column E2_EMISSAO as Date
column E2_VALOR as Numeric(16, 2)
SELECT E2_EMISSAO, E2_VALOR
FROM %table:SE2% SE2
WHERE E2_FILIAL = %xfilial:SE2%
AND SE2.%notDel%
EndSql
aInfo := GetLastQuery()
ConOut("Tempo de abertura: " + cValToChar(aInfo[5]))
(cAlias)->(DbCloseArea())Expected result
Fields reach AdvPL with the declared types and GetLastQuery() exposes execution details.
- Keep a WHERE clause so access filters can be added safely.
- Declare non-character result types with column.
- Use GetLastQuery() to inspect generated SQL and opening time.
- Place breakpoints before or after the block.
- Use a compatible DBAccess RPO and LIB.
- Evaluate functions before the block and pass the result through %exp%.
- Do not start a line with *; the precompiler may treat it as a comment.
- The ? character is reserved; consider FWPreparedStatement or FWExecStatement when it is required.
- Use a free dynamic alias and close it with DbCloseArea().
- %exp:% does not accept arrays or object values.
- Avoid %noparser% unless portability loss is deliberate.