SQL Snippets

SQL snippets are small pieces of SQL code that can be used and reused throughout Prepare instances to speed up development. SQL Snippets can be used when applying field level transformations. They can also be applied in Views, Stored Procedures, User Defined Functions, and Script Actions.

Add a SQL Snippet

1. Under Tools, select Snippets.

   

2. Click Add and then select SQL Snippet.

   

3. Provide a Name, Description, and Formula for the snippet. Highlight the parameter in the formula and click Add Parameter.

   

4. Under Parameters, change the Type to match what the parameter represents. You have the following options:

   • Table
   • Field
   • Database
   • User Defined Function
   • Stored Procedure
   • Value
5. Click OK.

Using a SQL Snippet

Apply the snippet using one of the following methods, which may vary depending on where or how the snippet is being used.

Field transformation

1. Expand the Scripts folder.
2. Right-click the field.
3. Select Add SQL Snippet Transformation.
4. Select the snippet from the list that appears on the right.

Stored procedure

1. Expand the Scripts folder.
2. Right-click Stored Procedures and then select Add Snippet Stored Procedure.
3. Select the snippet from the list that appears on the right.

User defined function

1. Expand the Scripts folder.
2. Right-click the User Defined Function folder and then select Add Snippet User Defined Function.
3. Select the snippet from the list that appears on the right.

Custom step

1. Expand the Scripts folder.
2. Right-click the Script Actions folder and then select Add Snippet Custom Step.
3. Select the snippet from the list that appears on the right.

View

1. Right-click the Views folder and then select Add Snippet View.
2. Select the snippet from the list that appears on the right.

Map parameters

1. Map the available fields to the parameters in the snippet. Drag the field(s) from the list on the right and drop the field on the Object Name/Value column for the relevant variable. The Object Name/Value column and Variant column will populate automatically.

   

2. Click OK.

Edit or Delete a Snippet

Under Tools, select Snippets. Select the snippet and click Edit or Delete.

Show Snippet Usage

Under Tools, select Snippets. Select the snippet and click Show Usage.

Troubleshooting

If the snippet is returning NULL values, for example in a custom transformation, and the table where the snippet is being applied is an incremental table, perform a full load to ensure that values are updated for the snippet.