How DBDocumentor ™ processes dynamic SQL

Beginning with DBDocumentor 3.10, the parsing of dynamic SQL occurs under a structured series of conditions.  Versions prior to 3.0 attempted to determine the intent behind the dynamic SQL in a fairly crude fashion.  This approach led to a large number of potential parsing error conditions which have been eliminated with structured Dynamic SQL Parsing Rules.  While these rules do create a situation where the output is fairly deterministic, due to the very nature of dynamic SQL, this still does not eliminate every potential parsing errors.  This section describes the rules followed for processing dynamic SQL and as such should provide an explanation of the outcome, if it is not immediately obvious.  DBDocumentor 4.00 expands upon this to provide the user with control over the types of dynamic SQL documented.  

Dynamic SQL Conditions Parsed

DBDocumentor will consider SQL which falls into one of these three categories as dynamic SQL, and will attempt to process it as such.

• SQL executed using EXEC sp_executesql 
• SQL executed inline 
• SQL executed inline from a string variable

If any of these three conditions are met, the SQL is considered dynamic and will be expanded.

SQL Dialect Specific Dynamic SQL

How dynamic SQL is specified varies from DBMS to DBMS.  The following DBMS statements constitute dynamic SQL for DBDocumentor.

Microsoft SQL Server

• EXEC sp_executesql 
• EXEC @sql_variable
• EXEC "dynamic SQL"

Firebird 1.5

• EXECUTE STATEMENT ""

Sybase SQL Anywhere (ASA)

• EXECUTE <procedure>
• EXECUTE "dynamic SQL"
• EXECUTE IMMEDIATE "dynamic SQL"
• CALL <procedure>

Dynamic SQL Expansion

DBDocumentor attempts to first expand any string variables contained within the dynamic SQL.  This expansion occurs to build up a pseudo command which can be parsed using the default SQL parser.  If the pseudo command corresponds to SQL which creates other objects, DBDocumentor will create sub-batches to handle the resultant SQL and its operations.

Dynamic SQL Error Conditions

During the processing of dynamic SQL, DBDocumentor applies some rudimentary validation rules on the pseudo command generated by the Dynamic SQL Parsing Rules.  These validation rules are intended to provide an indication of the types of problems which could be present in dynamic SQL, and if a rule appears to be broken, a log file entry will be written.  As a general rule, if you see an entry in the log file indicating a SQL warning, this indicates some dynamic SQL is present which may error out when executed.  If after investigation it is determined that the validation rule is in fact in error, please do not hesitate to inform Pikauba Software.

Dynamic SQL Output Inconsistencies

Since DBDocumentor does not evaluate the dynamic SQL, there is no method for it to provide 100% coverage of the possible paths of execution for the dynamic SQL.  What this means is that the output from the dynamic SQL parser may not be completely consistent with the output of the same SQL when run on SQL Server.  The following are examples of the expected inconsistencies:

• If the SQL to be executed is built up using several conditional statements (e.g. IF ... ELSE ), then only one of the paths will be parsed
• If the SQL to be executed is built up using CASE statements which impact the structure of the object (e.g. a CASE statement to select columns for a result set), then a warning will be issued to the log file, and the output may be inconsistent
• If the SQL to be executed is partially built from variables which are input to the procedure or function, the output may contain these variable names (e.g. creating a dynamically named table where the table name is passed into the procedure)
• If the tables to be joined on are determined conditionally, the data sources listed for the procedure or function may be incomplete.
• If a function is executed on a variable which is used for a data source, the name of the function will be returned.  For example:

'SELECT * FROM ' + RTRIM(@DBName)

Will return a data source of RTRIM

• If dynamic SQL is used to build additional dynamic SQL, then the output may not fully represent the actual SQL 

If any of these conditions are present, use of the override tag is recommended.

© 2001 - 2009 Pikauba Software. All rights reserved.

DBDocumentor and SQLDocumentation are trademarks of Pikauba Software.