Exibir origem

Dynamic SQL Register

Use this routine to register predetermined SQL queries for Dynamic Query execution. Use this resource to predefine SQL commands that you can run in the dynamic SQL execution screen. The register makes the execution of frequent scripts easier. Just select one from its register to run it.

The SQL scripts can have dynamic parameters, which will be forwarded automatically for SQL command. The parameters can be entered in the Dynamic query parameters routine or then generated dynamically in the execution.

The parameters should follow these rules:

•

General:

o	The parameter identification is done by character ':' (colon).

Example: select * from tabela where campo_inteiro = :codigo

o	The parameters to be entered in SQL should already be formatted.

▪	In case of date, time or character parameters, they must be entered between quotations.

Example: select * from tabela where campo_descricao = ‘:parametro_descricao’

▪	The numeric parameters should not be between quotations.

Example: select * from tabela where campo_inteiro = :codigo

•	Specific (dynamic, without file in the Parameters for dynamic query routine):

o	Parameters with initials ':I' will be interpreted as parameters that can only receive numeric characters;

Example: select * from tabela where campo_inteiro = :ICodigo_Do_Registro

o	Parameters with initials ':S' will be interpreted as parameters that will accept any character type;

Example: select * from tabela where campo_descricao = ‘:SParametro_Para_Campo_Descricao’

o	Parameters with initials ':D' will be interpreted as date type, formatting its content for the correct standard

Example: select * from tabela where campo_data >= ‘:DData_De_Abertura’

o	Parameters with initials ':D1' and ':D2' will be interpreted as one 'From until' assembling two fields for selection.

Example: select * from tabela where campo_data between ‘:D1Dt._Entrada_(ini)’ and ‘:D2Dt._Entrada_(fim)’

Linha Microsiga Protheus - Inglês > Dynamic SQL Register-praa902_parametros_consulta_dinamica.htm > help_button.zoom40.gif Important

One cannot enter SQL commands that are part of some transference or modification type in the database, such as UPDATE, DELETE, DROP, CREATE and so on.

Example of file:

Code: 1

Description: Customers' query

SQL: select * from tab_clientes

Code: 2

Description: Customers query by state

SQL: select * from tab_clientes where uf_cliente = ‘:SEstado_Do_Cliente’

Procedures

The dynamic SQL file can be done as follows:

1.	In Dynamic SQL file, select the Add option.

2.	Enter data according to help field instructions.

3.	Check the data and confirm the addition.

Linha Microsiga Protheus - Inglês > Dynamic SQL Register-praa902_parametros_consulta_dinamica.htm > help_button.zoom40.gif Note:

The SQL commands cannot modify the system datum structure, they may be queries only. The entered parameters create the filter in accordance with the where clause entered in the SQL command.