EXECUTE

The EXECUTE and the EXECUTE WITH statements allows queries to call other queries.  

EXECUTE

The EXECUTE statement allows queries to call other queries without passing any parameters.  The query called by the EXECUTE statement may be:

 

 

EXECUTE <query>;

 

 

EXECUTE [[ <query> ]];

 

 

EXECUTE [[ <query> ]] ON <data-source>;

 

EXECUTE WITH

The EXECUTE WITH statement allows queries to specify parameter values for the query being called, including the passing of table parameters.  Query parameters use @ as a prefix.

 

 

EXECUTE WITH (@<parameter> <type> = <expression>, ...) <query>;

 

 

EXECUTE WITH (@<parameter> <type> = <expression>, ...) [[ <query> ]];

 

 

EXECUTE WITH (@<parameter> <type> = <expression>, ...) [[ <query> ]] ON <data-source>;

 

 

EXECUTE WITH (@<parameter> <type> = <expression>, ...) CALL <table-function>;

 

 

EXECUTE Example

Suppose we have a query called p as follows:

 

SELECT * FROM [mfd_root] WHERE [mfd_id] >= 1;

 

We could run it from a command window with:

 

EXECUTE [p];

 

If the query p were located in a data source we could run it from a command window using the usual syntax to refer to components within data sources:

 

EXECUTE [datasource]::[p];

 

EXECUTE WITH Examples

Suppose in the Project pane we create a query named q that contains the following SQL text:

 

SELECT [city], [state]

FROM [authors]

WHERE [state] = @statename;

 

The query takes a parameter named statename, which is prefixed in the query with a @ character to indicate it is a parameter.

 

We can open a Command Window and run the following EXECUTE WITH query:

 

EXECUTE WITH (@statename NVARCHAR = 'CA') [q];

 

The EXECUTE WITH query calls the q query and passes the text value CA to that query  as the value to use for the @statename parameter.   The results table will list the city and state fields for all records in the authors table for which the state is CA.

 

Note the declaration as NVARCHAR for the data type of the @statename parameter in the EXECUTE WITH query.   The data type declared has to match the data type used within the query that is called.   For example, if the state field in the authors table was an INT16 number, and not NVARCHAR text, then the declaration of the @statename parameter being passed as NVARCHAR would be an error.    We do not need to specify the parameter type within the q query, since the parameter data type will be passed by any caller.

 

Consider the following Manifold query, which takes a parameter named minid.  The minid parameter is prefixed with a @ character to indicate it is a parameter:

 

SELECT * FROM [mfd_root] WHERE [mfd_id] >= @minid;

 

Suppose the query above is called q.  We can use it within the following query, which returns all tables with IDs greater than or equal to 2:

 

SELECT * FROM (EXECUTE WITH (@minid INT32 = 2) [q])

WHERE [type] = 'table';

 

Note the declaration of the minid type as INT32, matching how the parameter is used within the q query.

 

Consider a query named NamedStuff that contains the following text:

 

SELECT * FROM [mfd_root]

WHERE [name] LIKE @namePattern;

 

We use a @ prefix before namePattern to indicate it is a parameter.

 

We can invoke the NamedStuff query from either the Command Window or from another query with:

 

EXECUTE WITH (@namePattern NVARCHAR = 'mfd%') [NamedStuff];

 

Note the declaration of the parameter type in the above as NVARCHAR.

 

Table Parameters Examples

When passing table parameters EXECUTE WITH parameters are declared before the body.  The parameter declaration includes parameter types.

 

Suppose we have a query component called q that contains the following query:

 

SELECT * FROM @p WHERE mfd_id=@x;

 

To display a line from the mfd_root table where the value of the mfd_id field is 2 we could use the following command:

 

EXECUTE WITH (@p TABLE=mfd_root, @x INT32=2) q;

 

Or, we could use an inline text version of EXECUTE WITH that does the same:

 

EXECUTE WITH (@p TABLE=mfd_root, @x INT32=2)

[[ SELECT * FROM @p WHERE mfd_id=@x ]];

 

Passing table parameters to queries run on query engines other than Manifold will not work.

 

The following query works OK because we are passing a parameter @n from the EXECUTE WITH to the TABLE clauses:

 

FUNCTION f(@T TABLE) TABLE AS

  (SELECT Max([Population]) FROM @T) END;

EXECUTE WITH (@n TABLE = CALL f([States Table])) [[ TABLE @n; ]]  ;

 

This query does not work because what is inside the [[  ]] brackets is in a separate execution context that cannot see the function definition that comes before the EXECUTE WITH statement:

 

FUNCTION f(@T TABLE) TABLE AS

  (SELECT Max([Population]) FROM @T) END;

EXECUTE WITH (@n TABLE = [States Table]) [[ TABLE CALL f(@n); ]]  ;

 

If we wanted to use the function, we could define it within the [[  ]] brackets.  This works OK:

 

EXECUTE WITH (@n TABLE = [States Table])  [[

    FUNCTION f(@T TABLE) TABLE AS

      (SELECT Max([Population]) FROM @T) END;

    TABLE CALL f(@n);

]] ;

 

Inline Queries

The EXECUTE statement allows use of inline queries. The text of the inline query must  be enclosed in double square brackets [[  ]].   The use of double square brackets removes the need to escape single quotes within the inline query.

 

Inline query options:

 

 

The Manifold engine can decide to ignore a specified FETCH limit.   For example, Manifold will usually ignore the limit if the actual number of returned records was small enough to be retrieved in a single fetch from the data source.

 

Nesting of EXECUTE statements within [[ ... ]] inline queries is not allowed. If we have one or more EXECUTE statements each should be in its own query component.

 

Inline Query Example

 

The following query will run on [datasource] using the native query engine for that data source.   Because the query uses Manifold syntax, it will run perfectly if [datasource] is a Manifold .map file.  But if [datasource] is not a Manifold .map file but is something else, such as a PostgreSQL database, the query will likely fail or return incomplete data or data from the wrong table.

 

EXECUTE [[ SELECT * FROM [mfd_root]; ]] ON [datasource];

 

When executing inline queries on non-Manifold data sources, we should take care to write SQL that is legal for the native query engine that will be used.   Another possibility is to use the $manifold$ directive to force use of the Manifold query engine.    For example, the following query will always use the Manifold query engine:

 

EXECUTE

[[

-- $manifold$

SELECT * FROM [mfd_root];

]]

ON [datasource];

 

In the above the Manifold query engine will have to bring data in from the data source for the query engine to use.  If we want to leverage the data source's ability to run queries to distribute processing into the data source, it may make more sense to write the query using native syntax so it can run in the data source.

 

EXECUTE

[[

-- $manifold$

SELECT * FROM [mfd_root];

]]

ON [datasource]

FETCH 5;

 

The query above includes the FETCH clause, to bring in only the first 5 returned records.

Parameter Values and Inline Queries

To specify parameter values in inline queries we use WITH.

 

If the inline query is going to be run on the Manifold query engine, no special quoting of parameter names inside the query is required. If the inline query is going to be run on a non-Manifold query engine, we must enclose parameter names inside the query in @...@ brackets.

 

Examples

 

Passing parameters to a Manifold query:

 

EXECUTE WITH (@x INT32 = 1)

[[ SELECT * FROM [mfd_root] WHERE [mfd_id]=@x; ]];

 

Passing parameters to a SQL Server query:

 

EXECUTE  WITH (@x INT32 = 2, @y INT32 = 20)

[[ INSERT INTO dbo.t (a, b) VALUES (@x@, @y@) ]]

ON [sql];

 

Context for Inline Queries and ON Clause

Queries in the Command Window execute in the context that the Command Window was launched, by default in the root of the project, that is, locally.  We can launch the Command Window in the context of a data source if desired.    See the discussion of Command Window Context in the Command Window topic.  

 

An ON clause also modifies the context in which query text is run, but the ON clause only applies to the inline text of the query.  When an ON clause is used with EXECUTE WITH the context specified by ON will not apply to parameters that are passed.  When an ON clause is used with EXECUTE and a query function, the ON context will not apply to arguments for the function.

 

Consider a project that contains a drawing showing provinces in Mexico.  

 

 

The drawing's table is called Mexico Table.   It contains a few fields such as a Population field in addition to the Geom field used by the drawing.

 

 

The project also contains a simple query called MexQ:

 

SELECT * FROM [Mexico Table];

 

We save the project in a Manifold .map file and then we close the project.      We use File - New to create a new, blank project.  

 

 

In that new project we create a data source called Mexico using the .map file that was saved.  We will use this project for the following examples.  In all cases we launch the Command Window using View - New Command Window - SQL so it is launched in the default context of the root of the project.   We first consider two examples of queries involving the data source that do not use ON.

 

 

We can execute the MexQ query within the data source with:

 

EXECUTE [Mexico]::[MexQ];

 

There is no need to use ON to set the context since the query is already within the data source.

 

 

Likewise, if we want to use a query function such as:

 

FUNCTION f(@T TABLE) TABLE AS

 (SELECT Max([Population]) FROM @T) END

TABLE CALL f([Mexico]::[Mexico Table]);

 

...we pass the name of the target table within the data source, [Mexico]::[Mexico Table] , as an argument to the function.  That tells the system to go into that data source to find the table and allows it to find the [Population] field used in the function.

 

We now consider two cases of using ON, first a correct use:

 

 

The query:

 

EXECUTE [[

  SELECT Max([Population]) FROM [Mexico Table];

  ]]

  ON [Mexico];

 

... takes an inline query:

 

SELECT Max([Population]) FROM [Mexico Table];

 

... and runs it on the Mexico data source using the native query engine of that data source.  In this example since the data source is a Manifold .map file the native query engine of the data source is also Manifold.    Running the query "on [Mexico]" means that when the SELECT statement searches for a table named [Mexico Table], it does that search within the [Mexico] data source.  Within that data source the query engine finds the table named [Mexico Table], and it finds the field named [Population] and so the query works.

 

We now consider an incorrect use of the ON clause:

 

 

The query:

 

EXECUTE WITH (n TABLE = [Mexico Table]) [[

  SELECT Max([Population]) FROM n;

  ]]

  ON [Mexico];

 

... is incorrect because the context of the ON clause is not applied to parameters passed to an inline query.   ON only applies to the inline query text.     In this case when the query engine prepares parameters, it sees only one parameter, n, which is set to [Mexico Table].  Since the ON clause does not apply to parameters, the query engine looks for a table called [Mexico Table] in the context of the main query, which is the root of the project.   Since there is no such table there the query cannot find a [Population] field and the query fails.

 

As an alternative, we could simply use:

 

The query:

 

SELECT Max([Population])

  FROM [Mexico]::[Mexico Table];

 

... refers to [Mexico Table] within the [Mexico] data source so there is no problem finding the [Population] field.

 

An example using ON when passing parameters via EXECUTE WITH:

 

 

In the query:

 

EXECUTE WITH (@n int32 = 2000000) [[

  SELECT [Name], [Population] FROM [Mexico Table]

    WHERE [Population] > @n;

  ]]

  ON [Mexico];

 

... we are simply passing a number as a parameter, and not the name of a table that depends upon the execution context to be found.

 

Notes

Brackets and Parentheses - [ ] square brackets or ` ` reverse quotes are used for names.   ( ) parentheses are used for grouping language constructs.  { } curly brackets are not used in Manifold SQL but often appear in various other usages such as JSON.  [[ ]] double square brackets act as fences to isolate what is within.  For example, when inline queries are enclosed within double square brackets [[  ]] the use of double square brackets removes the need to escape single quotes within the inline query.  @ @ brackets are used to enclose parameter names inside inline queries that are going to be run on a non-Manifold query engine.

See Also

Tables

 

Functions

 

Queries

 

Regular Expressions

 

Command Window

 

Command Window - Query Builder

 

SQL Functions

 

SQL Operators

 

Temporary Databases

 

FETCH