# Stored Procedures

## General Characteristics of Stored Procedures

As noted in [Enabling Versioned Views](/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/database-design/4.6-enabling-versioned-views.md), database views allow useful queries to be saved and reused as though they were database tables. The SQL queries used in a view can be complex and can include data from other views such as those described in [Composite Views](/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/database-design/4.8-composite-views.md). However, the definition of each view is defined by a single SQL query.

Stored procedures and functions provide an another way to define reusable resources in a database. The key difference between these database views, stored procedures and functions are summarized in [Table 1](#user-content-fn-1)[^1]. From a practical perspective these differences enable stored procedures to facilitate some types of access to a SNOMED CT data that cannot be supported by using database views. The following subsections describe a few examples of stored procedures that are included in the SNOMED CT example database.

**Table 1: Features of Views, Stored Procedures and Functions (in MySQL)**

<table data-header-hidden><thead><tr><th width="341.5859375"></th><th></th><th></th><th></th></tr></thead><tbody><tr><td><strong>Feature</strong></td><td><strong>Database View</strong></td><td><strong>Stored Procedure</strong></td><td><strong>Stored Function</strong></td></tr><tr><td>Enable definition of reusable resources that facilitate commonly required processes that access data without</td><td><div><figure><img src="/files/yN6YP6KzPAXkI1xrbq0h" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/M5koXS0bwFYrtRiPdVMn" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/Tb5nXC7JIyIWG1Kb9TTX" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td>Defined by a single query</td><td><div><figure><img src="/files/K0iEvOPXqUPsb4Vc6otq" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/xr11Qtms46gKiawuixqk" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/BodHEw8rYil2EODAp0N5" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td>Produce output that can queried in the same way as a database table</td><td><div><figure><img src="/files/sVhXrcTbkcYkXc462i8K" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/0k280dPwFhN7U7mt4YYc" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/KWmKYxvHW9rpLWUIJ1oK" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td>Can be defined to output the results of a single SQL query</td><td><div><figure><img src="/files/O9lPCC5BsjRdOG66L3kw" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/gz96UJSVIbnoxH9Z3jJT" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/wW7MZiYwWvkjXCRuWA2K" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td>Can be defined to output the results of one or more SQL queries</td><td><div><figure><img src="/files/DsPLNuJGnFFJyOPwGgwG" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/jWKpuYCkHs1NiIF7iasj" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/aN8EDzAi4lupwITkzB3s" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td><a data-footnote-ref href="#user-content-fn-2">Can be defined to add, delete or alter data in a table</a></td><td><div><figure><img src="/files/RO5yCNFUcr1Q9ujZT2bH" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/pn7a1JQ7NmDQcVa9qOVS" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/YuCERA4OjZ7dVa11KYvj" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td><a data-footnote-ref href="#user-content-fn-2">Can be defined to include transactional SQL statements</a></td><td><div><figure><img src="/files/WkiFvTimv7jbcNkvhufP" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/oDOe6r56Uhx3x0XVHoab" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/5fQBd3YeukVBe9r899w7" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td><a data-footnote-ref href="#user-content-fn-2">Can create, alter or delete database tables, views, procedures or functions</a></td><td><div><figure><img src="/files/ASJ5uO0pNEUbXCLALGPD" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/QskIQReT6fWxG9y4dUVo" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/rAIfrnJnViEASAgBExoC" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td>Can be defined with input parameters to be set when invoked with values that affect the results</td><td><div><figure><img src="/files/WGHOHlPPHmgRH2h2m5gb" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/ixEmpdd3rSkTEdQ5dxZT" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/e4oyLJ23Mv7HYBIrtpOu" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td>Can be defined to set values the values of one or more output parameters</td><td><div><figure><img src="/files/YMzUBSAe6dy8o6ODUFpm" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/Gc7AQiYazBp2RQD47HAd" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/7BfuoKnlaohdB2ShjXDV" alt=""><figcaption></figcaption></figure></div></td></tr><tr><td>Can be defined to return a single value of a specified datatype</td><td><div><figure><img src="/files/hXh4kWIWQ8rtyre0xom1" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/aIr2kUEBGvlWmNVjQ9eZ" alt=""><figcaption></figcaption></figure></div></td><td><div><figure><img src="/files/YTlYhNi8rlWBen1iUHSM" alt=""><figcaption></figcaption></figure></div></td></tr></tbody></table>

## Configuration Procedures

The SNOMED CT example database, has includes configuration settings that control configurable aspects of versioned table views (see[ Versioned Database Table Views](/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/database-design/4.6-enabling-versioned-views.md#title-text)). The configuration settings also affect queries, composite views and procedures that refer to configurable versioned table views.

The configuration settings are represented by a database table called config\_settings and each of the procedures described in this section either selects data from that table or updates data in the table. The details and default settings of the config\_settings table are shown in Table 2.

**Table 2: Configuration Table Specification and initial settings**

<table data-header-hidden><thead><tr><th width="154.1796875"></th><th width="348.265625"></th><th width="143.20703125"></th><th width="215.8671875"></th><th width="254.15234375"></th></tr></thead><tbody><tr><td><strong>Column</strong></td><td><strong>Description</strong></td><td><strong>Datatype</strong></td><td><strong>Permitted Values</strong></td><td><strong>Default Values</strong></td></tr><tr><td>id</td><td><p>Identifies the configuration setting and links to directly to view name prefixes</p><ul><li>id=0 refers to snap and delta views (the time values are fixed for these views)</li><li>id=1 refers to snap1 and delta1 views</li><li>id=2 refers to snap2 and delta2 views</li></ul></td><td>TINYINT</td><td>Integer in range 0-255</td><td>0, 1, 2</td></tr><tr><td>languageId</td><td>The refsetId of a language reference set.</td><td>BIGINT</td><td>Any language refsetId (e.g. 900000000000509007, 900000000000508004)</td><td>900000000000509007</td></tr><tr><td>languageName</td><td>The name of the language represented by the language reference set identified by languageId.</td><td>VARCHAR(255)</td><td>The name of any language or dialect represented by a language refset (e.g. US English, GB English).</td><td>US English</td></tr><tr><td>snapshotTime</td><td><a data-footnote-ref href="#user-content-fn-3">The snapshotTime for views with the relevant view name prefix</a>.</td><td>DATETIME</td><td><ul><li>Id=0: Release date (fixed)</li><li>Id>1: Any valid date.</li></ul></td><td><ul><li>Id=0: Release date</li><li>Id=1: Six months before release date</li><li>Id=2: One year before release date.</li></ul></td></tr><tr><td>deltaStartTime</td><td><a data-footnote-ref href="#user-content-fn-3">The effectiveTime of a component or refset member row must be greater than deltaStartTime to be included in the delta view with the relevant prefix</a>.</td><td>DATETIME</td><td><ul><li>Id=0: Six months before release date (fixed)</li><li>Id>1: Any valid date before the release date.</li></ul></td><td><ul><li>Id=0: Six months before release date</li><li>Id=1: One year before release date</li><li>Id=2: Eighteen months before release date.</li></ul></td></tr><tr><td>deltaEndTime</td><td><a data-footnote-ref href="#user-content-fn-3">The effectiveTime of a component or refset member row must be less than or equal to the deltaStartTime to be included in the delta view with the relevant prefix</a>.</td><td>DATETIME</td><td><ul><li>Id=0: Release date (fixed)</li><li>Id>1: Any valid date after the deltaStartTime.</li></ul></td><td><ul><li>Release DateTime (fixed)</li><li>Id=1: Six months before release date</li><li>Id=2: One year before release date.</li></ul></td></tr></tbody></table>

### Show Configuration Procedure <a href="#id-4.9.2.configurationprocedures-showconfigurationprocedure" id="id-4.9.2.configurationprocedures-showconfigurationprocedure"></a>

The **showConfig** procedure selects and displays the configuration files data.

**Example 1: Using the showConfig Procedure**

**SQL Call to Procedure**

```sql
call showConfig();
```

[**Result**](#user-content-fn-4)[^4]

<table data-header-hidden><thead><tr><th></th><th width="211.109375"></th><th width="145.92578125"></th><th width="196.0390625"></th><th width="142.2578125"></th><th width="144.73046875"></th><th width="135.5625"></th></tr></thead><tbody><tr><td><strong>id</strong></td><td><strong>languageId</strong></td><td><strong>languageName</strong></td><td><a data-footnote-ref href="#user-content-fn-5"><strong>refsetName</strong></a></td><td><strong>snapshotTime</strong></td><td><strong>deltaStartTime</strong></td><td><strong>deltaEndTime</strong></td></tr><tr><td>0</td><td>900000000000509007</td><td>US English</td><td>United States of America English language reference set (foundation metadata concept)</td><td>2019-07-31</td><td>2019-01-31</td><td>2019-07-31</td></tr><tr><td>1</td><td>900000000000509007</td><td>US English</td><td>United States of America English language reference set (foundation metadata concept)</td><td>2019-01-31</td><td>2018-07-31</td><td>2019-01-31</td></tr><tr><td>2</td><td>900000000000509007</td><td>US English</td><td>United States of America English language reference set (foundation metadata concept)</td><td>2018-07-31</td><td>2018-01-31</td><td>2018-07-31</td></tr></tbody></table>

### Set Language <a href="#id-4.9.2.configurationprocedures-setlanguage" id="id-4.9.2.configurationprocedures-setlanguage"></a>

The **setLanguage** procedure sets the languageId and languageName for a configuration row specified by its identifier value.

{% hint style="warning" %}
Languages can only be set if the following conditions apply.

1. The concept identifying the language reference set is available in the database.
2. The language abbreviation, language name and the identifier of the language reference set are in the config\_language table.
3. The identified language reference set is available in the snap\_refset\_language reference set table or view.
   {% endhint %}

| **SQL Call to Procedure**                                    |
| ------------------------------------------------------------ |
| call setDeltaRange(p\_id,p\_deltaStartTime,p\_deltaEndTime); |

| **Parameter** | **Description**                                                                                                                     | **Data type** | **Valid values**                                                             | **Example** |
| ------------- | ----------------------------------------------------------------------------------------------------------------------------------- | ------------- | ---------------------------------------------------------------------------- | ----------- |
| p\_id         | The identifier of the configuration table view (also the number of the snapshot or delta view number to which the setting applies). | TINYINT       | <p>0, 1 or 2</p><p>The language setting can be changed for the id=0 row.</p> | 1           |
| p\_lang\_code | The date after which changes will be included in the delta view.                                                                    | VARCHAR(5)    | Any value that MySQL recognizes as a date or date-time.                      | 'en-GB'     |

<table data-header-hidden><thead><tr><th></th></tr></thead><tbody><tr><td><strong>Example Procedure Call</strong></td></tr><tr><td><pre class="language-sql"><code class="lang-sql">call setLanguage(1,'en-GB');
</code></pre></td></tr><tr><td><strong>Result</strong></td></tr><tr><td><p>If p_lang_code does not refer to a language code in the config_language file or if no valid refset or refset members are found the procedure reports an error.</p><p>If the procedure succeeds, the language setting is changed but there is no output data. To check the result of the change, call showConfig() after resetConfig.</p></td></tr></tbody></table>

### Set Snapshot Time <a href="#id-4.9.2.configurationprocedures-setsnapshottime" id="id-4.9.2.configurationprocedures-setsnapshottime"></a>

The **setDeltaRange** procedure sets the *snapshotTime* for a configuration row specified by its id value.

| **SQL Call to Procedure**                                  |
| ---------------------------------------------------------- |
| call setDeltaRange(p\_id,p\_snapshotTime,p\_deltaEndTime); |

<table data-header-hidden><thead><tr><th width="201.90625"></th><th width="266.4140625"></th><th></th><th></th><th></th></tr></thead><tbody><tr><td><strong>Parameter</strong></td><td><strong>Description</strong></td><td><strong>Data type</strong></td><td><strong>Valid values</strong></td><td><strong>Example</strong></td></tr><tr><td>p_id</td><td>The identifier of the configuration table view (also the number of the snapshot or delta view number to which the setting applies).</td><td>TINYINT</td><td><p>1 or 2</p><p>(Note: Values less than 1 or greater than 2 will be treated as referring to row 2)</p></td><td>1</td></tr><tr><td><pre><code>p_snapshotTime
</code></pre></td><td>The date for which the identified snapshot view will be computed.</td><td>DATETIME</td><td>Any value that MySQL recognizes as a date or date-time.</td><td>'2017-07-31'</td></tr></tbody></table>

<table data-header-hidden><thead><tr><th></th></tr></thead><tbody><tr><td><strong>Example Procedure Call</strong></td></tr><tr><td><pre class="language-sql"><code class="lang-sql">call setSnapshotTime(1,'2017-07-31');
</code></pre></td></tr><tr><td><strong>Result</strong></td></tr><tr><td>The snapshotTime setting is changed but there is no output data. To check the result of the change, call showConfig() after resetConfig.</td></tr></tbody></table>

### Set Delta Range <a href="#id-4.9.2.configurationprocedures-setdeltarange" id="id-4.9.2.configurationprocedures-setdeltarange"></a>

The **setDeltaRange** procedure sets the *deltaStartTime* and *deltaEndTime* for a configuration row specified by its id value.

| **SQL Call to Procedure**                                    |
| ------------------------------------------------------------ |
| call setDeltaRange(p\_id,p\_deltaStartTime,p\_deltaEndTime); |

<table data-header-hidden><thead><tr><th width="179.21875"></th><th width="262.234375"></th><th></th><th width="209.80859375"></th><th></th></tr></thead><tbody><tr><td><strong>Parameter</strong></td><td><strong>Description</strong></td><td><strong>Data type</strong></td><td><strong>Valid values</strong></td><td><strong>Example</strong></td></tr><tr><td>p_id</td><td>The identifier of the configuration table view (also the number of the snapshot or delta view number to which the setting applies).</td><td>TINYINT</td><td><p>1 or 2</p><p>(Note: Values less than 1 or greater than 2 will be treated as referring to row 2)</p></td><td>1</td></tr><tr><td>p_deltaStartTime</td><td>The date after which changes will be included in the delta view.</td><td>DATETIME</td><td>Any value that MySQL recognizes as a date or date-time.</td><td>'2016-07-31'</td></tr><tr><td>p_deltaEndTime</td><td>The date on or before which changes with be included in the delta view.</td><td>DATETIME</td><td>Any value that MySQL recognizes as a date or date-time.</td><td>'2019-01-31'</td></tr></tbody></table>

<table data-header-hidden><thead><tr><th></th></tr></thead><tbody><tr><td><strong>Example Procedure Call</strong></td></tr><tr><td><pre class="language-sql"><code class="lang-sql">call setDeltaRange(1,'2016-07-31','2017-07-31');
</code></pre></td></tr><tr><td><strong>Result</strong></td></tr><tr><td>The deltaStartTime and deltaEndTime settings are changed but there is no output data. To check the result of the change, call showConfig() after resetConfig.</td></tr></tbody></table>

### Reset Configuration <a href="#id-4.9.2.configurationprocedures-resetconfiguration" id="id-4.9.2.configurationprocedures-resetconfiguration"></a>

The **resetConfig** procedure resets all the configuration settings to the default values shown in the example below.

{% hint style="danger" %}
The reset depends on the date times config\_settings on the row with id=0 being unchanged. In particular, if assumes the snapshotTime of that row as the releaseDate. The other procedures described in this section do not change those values. However, if those values are changed by update queries the resetConfig procedure will not correctly reset the snapshot and delta times.
{% endhint %}

**Example : Reset Configuration**

<table data-header-hidden><thead><tr><th></th></tr></thead><tbody><tr><td><strong>SQL Call to Procedure</strong></td></tr><tr><td><pre class="language-sql"><code class="lang-sql">call resetConfig();
</code></pre></td></tr><tr><td><strong>Result</strong></td></tr><tr><td>The reset is performed but there is no output data. To check the result of the reset, call showConfig() after resetConfig.</td></tr></tbody></table>

## Language and Dialect Comparison Procedures

### Terms in Languages

The **termsInLanguages** procedure displays the terms associated with a list of selected concepts in one or more specified languages or dialects. The concepts to be selected are specified by a comma-separated list of conceptIds and the languages in which they are to be displayed are specified by a comma-separated list of language codes.

**Example: Example Use of termsInLanguages Procedure**

<table data-header-hidden><thead><tr><th></th></tr></thead><tbody><tr><td><strong>SQL Call to Procedure</strong></td></tr><tr><td><pre><code>call snap_termsInLanguages(p_conceptids,p_langCodes);
</code></pre></td></tr></tbody></table>

<table data-header-hidden><thead><tr><th width="220.77734375"></th><th width="443.68359375"></th><th></th><th width="222.953125"></th></tr></thead><tbody><tr><td><strong>Parameter</strong></td><td><strong>Description</strong></td><td><strong>Data type</strong></td><td><strong>Examples</strong></td></tr><tr><td><pre><code>p_conceptids
</code></pre></td><td>A string containing a comma separated list of concept identifiers.</td><td>text</td><td><pre><code>'80146002,49438003'
</code></pre></td></tr><tr><td><pre><code>p_langCodes
</code></pre></td><td><p>A string containing a comma separated list of language codes.</p><p>Only languages and dialects in the release files can be selected. With the International Release can be tested with p_langCode set to 'en-GB,en-US'.<br>If other description files with terms in other languages are imported along with the relevant language refsets then these languages will also be accessible.</p><p><br></p></td><td>text</td><td><pre><code>'en-GB,en-US'
</code></pre></td></tr></tbody></table>

| **Example Procedure Call**                                    |
| ------------------------------------------------------------- |
| `call snap_ShowLanguages('80146002,49438003','en-GB,en-US');` |

**Result**

| **conceptId** | **type\_and\_lang** | **term**                               |
| ------------- | ------------------- | -------------------------------------- |
| 80146002      | FSN en-GB           | Excision of appendix (procedure)       |
| 80146002      | Preferred en-GB     | Appendicectomy                         |
| 80146002      | Synonyms en-GB      | Excision of appendix                   |
| 80146002      | FSN en-US           | Excision of appendix (procedure)       |
| 80146002      | Preferred en-US     | Appendectomy                           |
| 80146002      | Synonyms en-US      | Excision of appendix                   |
| 49438003      | FSN en-GB           | Appendectomy with drainage (procedure) |
| 49438003      | Preferred en-GB     | Appendicectomy with drainage           |
| 49438003      | Synonyms en-GB      | Appendicectomy and drainage            |
| 49438003      | FSN en-US           | Appendectomy with drainage (procedure) |
| 49438003      | Preferred en-US     | Appendectomy with drainage             |
| 49438003      | Synonyms en-US      | Appendectomy and drainage              |

## Search Procedures

### Search Plus Procedures

The **searchPlus** procedure lists the conceptId and terms that match a search pattern. The search matches can also be filtered using a simple focus concept expression constraint or a regular expression pattern.

{% hint style="warning" %}
Note

This procedure is intended to demonstrate some of the search options available in MySQL and to illustrate the value of restricting searches to subtypes of concepts that in particular hierarchies or sub-hierarchies.

Additional work would be required to make a more user-friendly interface to these search facility and this is beyond the scope of the SNOMED CT example database.
{% endhint %}

**Example Use of searchPlus Procedure**

| **SQL Call to Procedure**                    |
| -------------------------------------------- |
| call snap\_searchPlus(p\_search, p\_filter); |

<table data-header-hidden><thead><tr><th></th><th width="597.1875"></th><th></th><th width="248.5859375"></th></tr></thead><tbody><tr><td><strong>Parameter</strong></td><td><strong>Description</strong></td><td><strong>Data type</strong></td><td><strong>Examples</strong></td></tr><tr><td>p_search</td><td><p>A search term string.</p><p>These searches use MySQL's fulltext index employing one of the two supported search modes depending on the string supplied.</p><p>If p_search includes plus "+" or minus "-" symbols, the search is performed using boolean mode. This means matches are only returned if all words preceded by "+" are present and none of the words "-". Word not preceded by either plus or minus, are not required to match but will contribute to the search. The results of this search are sorted by with the shortest matching terms first.</p><p>If p_search does not include "+" or "-" symbols, the MySQL natural language search is used. It also orders the returned results by 'relevance' and is intended to assist contextual searching through literature. However, our testing indicate that the boolean mode is usually more effective for SNOMED CT searches.</p><p>For more information about full text searches in MySQL please see <a href="https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html">Full-Text Search Functions in the MySQL Reference Manual</a>.</p></td><td>text</td><td><p>'+fundus +stomach'</p><p>'+lung +disease +chronic'</p><p>'appendix'</p><p>'hemoglobin'</p><p>'infection'</p></td></tr><tr><td>p_filter</td><td><p>A filter that will be applied to selectively include concepts that are subtype descendants of a specified concept:</p><ul><li><p>a simple focus concept subtype constraint.</p><ul><li><p>Starting with a less than sign <strong>&#x3C;</strong> followed either by either</p><ul><li>a conceptId; or</li><li>a shortcut abbreviation for a commonly used concept. To see the current set of shortcuts run the following query "SELECT * FROM config_shortcutPlus;"</li></ul></li></ul></li></ul><p>A regular expression pattern to be used to filter terms returned by the search string</p><ul><li><p>a regular expression to exclude matching terms</p><ul><li>Starting this with an exclamation mark ! followed by the regular expression you want to exclude for the terms found by p_search.</li></ul></li><li><p>a regular expression required for inclusion<br></p><ul><li>The text of the regular expression that must be matched for inclusion.</li></ul></li></ul></td><td>text</td><td><p>'&#x3C;find'</p><p>'&#x3C;proc'</p><p>'&#x3C;123037004'</p><p>'!lung'</p><p>'heart'</p></td></tr></tbody></table>

| <p><strong>Example Procedure Call</strong><br></p>                             |
| ------------------------------------------------------------------------------ |
| `call snap_searchPlus('+viral +infection','< 19829001 \|disorder of lung\|');` |
| **Result**                                                                     |

| **conceptId** | **term**                                                                      |
| ------------- | ----------------------------------------------------------------------------- |
| 75570004      | Viral pneumonia                                                               |
| 276692000     | Congenital viral pneumonia                                                    |
| 75570004      | Viral pneumonia (disorder)                                                    |
| 421508002     | Viral pneumonia associated with AIDS                                          |
| 276692000     | Congenital viral pneumonia (disorder)                                         |
| 421508002     | Viral pneumonia associated with acquired immunodeficiency syndrome            |
| 421508002     | Viral pneumonia associated with acquired immunodeficiency syndrome (disorder) |

## Expression Constraint Procedures

### ECL Query Procedure

The **eclQuery** lists the conceptId and preferred term for each concept that conforms to a specified **SNOMED CT expression constraint**.

{% hint style="warning" %}
**Notes**

1. The expression constraints supported do not cover the full ECL specification but are restricted as described below.
2. This procedure will only run in MySQL version 8.0 or later. It uses some function which are not available in earlier versions (including the widely used MySQL version 5.7).
   {% endhint %}

**Example Use of eclQuery Procedure**

| **SQL Call to Procedure**    |
| ---------------------------- |
| call snap\_eclQuery(p\_ecl); |

<table data-header-hidden><thead><tr><th width="119.1328125"></th><th width="192.015625"></th><th width="118.21875"></th><th width="324.84375"></th></tr></thead><tbody><tr><td><strong>Parameter</strong></td><td><strong>Description</strong></td><td><strong>Data type</strong></td><td><strong>Examples</strong></td></tr><tr><td>p_ecl</td><td>The ECL query text</td><td>text</td><td><p>(&#x3C; 19829001 |disorder of lung|) OR (&#x3C; 301867009 |edema of trunk|)</p><p>'(&#x3C; 19829001 |disorder of lung|) MINUS (&#x3C; 301867009 |edema of trunk|)'</p><p>'>> 40541001 |Acute pulmonary edema|'</p><p>'>39057004 |pulmonary valve|'</p><p>'>!39057004 |pulmonary valve|'</p></td></tr></tbody></table>

<table data-header-hidden><thead><tr><th></th></tr></thead><tbody><tr><td><strong>Example Procedure Call</strong></td></tr><tr><td><pre class="language-sql"><code class="lang-sql">call snap_eclQuery('&#x3C; 19829001 |disorder of lung|:116676008 |Associated morphology| = 40829002 |Acute edema|');
</code></pre></td></tr><tr><td><strong>Result</strong></td></tr></tbody></table>

| **conceptId**     | **term**                                           |
| ----------------- | -------------------------------------------------- |
| 10519008          | Acute pulmonary oedema due to fumes AND/OR vapours |
| 40541001          | Acute pulmonary oedema                             |
| 61233003          | Silo-fillers' disease                              |
| 233706004         | Drug-induced acute pulmonary oedema                |
| 233709006         | Toxic pulmonary oedema                             |
| 233710001         | Chemical-induced pulmonary oedema                  |
| 233711002         | Oxygen-induced pulmonary oedema                    |
| 360371003         | Acute cardiac pulmonary oedema                     |
| 10674871000119105 | Pulmonary oedema caused by chemical fumes          |

### Expression Constraint Feature Support and Limitations <a href="#id-4.9.5.expressionconstraintprocedures-expressionconstraintfeaturesupportandlimitations" id="id-4.9.5.expressionconstraintprocedures-expressionconstraintfeaturesupportandlimitations"></a>

The following notes provide a brief summary of the extent to which this procedure supports evaluation of expression constraints. For a full and detailed understanding of SNOMED CT expression constraints see the specification of the [SNOMED CT Expression Constraint Language](https://docs.snomed.org/snomed-ct-specifications/snomed-ct-expression-constraint-language/).

#### ECL Operators and Examples <a href="#id-4.9.5.expressionconstraintprocedures-ecloperatorsandexamples" id="id-4.9.5.expressionconstraintprocedures-ecloperatorsandexamples"></a>

<table data-header-hidden><thead><tr><th width="140.3125">Symbol</th><th>Name</th><th>Summary</th><th>Example</th></tr></thead><tbody><tr><td>&#x3C;</td><td>Descendant of</td><td>The set of all subtypes of the given concept</td><td>&#x3C; 404684003 |Clinical finding|</td></tr><tr><td>&#x3C;&#x3C;</td><td>Descendant or self of</td><td>The set of all subtypes of the given concept plus the concept itself</td><td>&#x3C;&#x3C; 73211009 |Diabetes mellitus|</td></tr><tr><td>></td><td>Ancestor of</td><td>The set of all supertypes of the given concept</td><td>> 40541001 |Acute pulmonary edema|</td></tr><tr><td>>></td><td>Ancestor or self of</td><td>The set of all supertypes of the given concept plus the concept itself</td><td>>> 40541001 |Acute pulmonary edema|</td></tr><tr><td>&#x3C;!</td><td>Child of</td><td>The set of all children of the given concept</td><td>&#x3C;! 195967001 |Asthma|</td></tr><tr><td>>!</td><td>Parent of</td><td>The set of all parents of the given concept</td><td>>! 195967001 |Asthma|</td></tr><tr><td>^</td><td>Member of</td><td>The set of referenced components in the given reference set</td><td>^ 733990004 |Nursing activities reference set|</td></tr><tr><td>*</td><td>Any</td><td>Any concept in the given SNOMED CT edition</td><td>*</td></tr><tr><td>:</td><td>Refinement</td><td>Only those concepts whose defining relationships match the given attribute value pairs</td><td>&#x3C; 404684003 |clinical finding|: 116676008 |associated morphology| = *</td></tr><tr><td>AND</td><td>Conjunction</td><td>Only those concepts in both sets</td><td>(&#x3C; 19829001 |disorder of lung|) AND (&#x3C; 301867009 |edema of trunk|)</td></tr><tr><td>OR</td><td>Disjunction</td><td>Any concept that belongs to either set</td><td>(&#x3C; 19829001 |disorder of lung|) OR (&#x3C; 301867009 |edema of trunk|)</td></tr><tr><td>MINUS</td><td>Exclusion</td><td>Concepts in the first set that do not belong to the second set</td><td>(&#x3C; 19829001 |disorder of lung|) MINUS (&#x3C; 301867009 |edema of trunk|)</td></tr></tbody></table>

#### Additional Notes on Limitations of the ECL Query Procedure <a href="#id-4.9.5.expressionconstraintprocedures-additionalnotesonlimitationsoftheeclqueryprocedure" id="id-4.9.5.expressionconstraintprocedures-additionalnotesonlimitationsoftheeclqueryprocedure"></a>

The following notes outline the extent to which this procedure supports the SNOMED CT expression constraints and highlights some of the most significant limitations of this procedure.

#### One or more constraints can be specified. <a href="#id-4.9.5.expressionconstraintprocedures-oneormoreconstraintscanbespecified" id="id-4.9.5.expressionconstraintprocedures-oneormoreconstraintscanbespecified"></a>

* Each constraint must start with a focus concept constraint expressed as one of the following:
  * A single conceptId specifying that concept as the focus concept.
  * A single conceptId preceded by **<** (specifying subtypes only) or **<<** (specifying self or subtypes)
  * A conceptId that identifies a reference set preceded by a **^** indicating members of that reference set.
  * An asterisk **\*** (indicating any concept)
* In all cases a concept Id may be followed by a term surrounded by pipe characters.
  * The term between pipes will be ignored for processing.
* Spaces between any elements in the expression will be ignored
* The focus constraint may optionally be followed by a refinement constraint separated from the focus constraint by a colon **:**

#### If present the refinement constraint must consist of one or more attribute-value-constraint pairs. <a href="#id-4.9.5.expressionconstraintprocedures-ifpresenttherefinementconstraintmustconsistofoneormoreattrib" id="id-4.9.5.expressionconstraintprocedures-ifpresenttherefinementconstraintmustconsistofoneormoreattrib"></a>

* The pair consists of an attribute-constraint and a value-constraint:\\
  * The attribute-constraint must be separated from the value constraint by an **=** (equals) sign
  * Both the constraints may be specified using any of the forms permitted for the focus concept constraint (see 1)
* Attribute attribute-value-constraint pairs must be separated by a comma from any following attribute-value-constraint pair
* **NOTE**: The procedure **does not** support:
  * Nested refinement constraints
  * Role grouping constraints
  * Cardinality constraints

#### If more than one constraint is specified: <a href="#id-4.9.5.expressionconstraintprocedures-ifmorethanoneconstraintisspecified" id="id-4.9.5.expressionconstraintprocedures-ifmorethanoneconstraintisspecified"></a>

* Each constraint must be enclosed in brackets
* One of the following logical operators must be present between adjacent constraints
  * OR The resulting set is the set of concepts that conform to **either** the constraint to the left or the constraint to the right (or both constraint).
  * AND The resulting set is the set of concepts that conform to **both** the constraint to the left and the constraint on the right.
  * MINUS The resulting set is the set of concepts that both conform to the constraint to the left and do **not** conform to the constraint on the right.
* **NOTE**: The procedure **does not** support the use of brackets to alter the order of evaluation of constraints in a set.\
  The set of constraints is evaluated from left to right and, as illustrated below, this is likely to affect the results. 1.
  1. **(A) OR (B) AND (C) MINUS (D)**
     1. Concepts in either (A) or (B) form temporary set (aT1)
     2. Concepts in (aT1) and also in (C) form temporary set (aT2)
     3. Concepts in (aT2) that are NOT in (D) form the final result set (aR)
     4. *One outcome of this order is that concepts in (B) that are **not** in (C) or are in (D) will not appear in the result set.*
  2. **(A) MINUS (D) AND (C) OR (B)**
     1. Concepts in (A) and NOT in (D) form temporary set (bT1)
     2. Concepts in (bT1) and also in (C) form temporary set (bT2)
     3. Concepts in either (bT2) or (B) for the final result set (bR)
     4. *In this order all concepts that are in (B) with me in the result set.*

#### Summary of Limitations of ECL Support for this Procedure <a href="#id-4.9.5.expressionconstraintprocedures-summaryoflimitationsofeclsupportforthisprocedure" id="id-4.9.5.expressionconstraintprocedures-summaryoflimitationsofeclsupportforthisprocedure"></a>

The Procedure **does not** support:

* Nested constraints
* Dotted attributes
* Nested refinement constraints
* Attribute group constraints
* Cardinality constraints

The Procedure also requires that:

* Even simple expression constraints must be enclosed by brackets when multiple constraints are combined.

<a href="https://docs.google.com/forms/d/e/1FAIpQLScTmbZIf0UEQwYDkY27EEWBkaiYkHSbR0_9DmFrMLXoQLyL7Q/viewform?usp=pp_url&#x26;entry.1767247133=SQL+Guide&#x26;entry.670899847=Stored%20Procedures" class="button primary">Provide Feedback</a>

[^1]: The features of stored procedures and functions shown in the table are those that apply to MySQL. Some of these features may differ in other database environments.

[^2]: Access to features that make changes to data or database resources may be limited by database security settings.

[^3]: Internally all these configuration dates are stored as the time 23:59:59 on the stated date. This ensure all changes on the end date are included in snapshot and delta views while all changes on the start date are excluded from a delta view.

[^4]: The results shown here are those the initial default settings for the 2019-07-31 release.

[^5]: The refsetName is selected by looking up the languageId in the snap\_fsn view.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.snomed.org/snomed-ct-practical-guides/snomed-ct-sql-practical-guide/database-design/4.9-stored-procedures.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.