Microsoft Azure Synapse SQL Connector > Mappings and mapping tasks with Microsoft Azure Synapse SQL > Process SQL queries and stored procedures using an SQL transformation
  

Process SQL queries and stored procedures using an SQL transformation

You can configure an SQL transformation to process SQL queries and stored procedures midstream in a Microsoft Azure Synapse SQL mapping. When you add an SQL transformation, you define the database connection and the type of SQL that the transformation processes.
You cannot configure a SQL transformation in an elastic mapping.
The SQL transformation can process the following types of SQL statements:
Stored procedure
You can configure an SQL transformation to call a stored procedure in Microsoft Azure Synapse SQL. The stored procedure must exist in the Microsoft Azure Synapse SQL database before you create the SQL transformation. When the SQL transformation processes a stored procedure, it passes input parameters to the stored procedure. The stored procedure returns the values to the output fields of the transformation.
SQL Query
You can configure an SQL transformation to process an entered query that you define in the SQL editor. The SQL transformation processes the query and returns the rows. The SQL transformation also returns any errors that occur from the underlying database or if there is an error in the user syntax.
Note: Saved query type is not applicable.
For more information about SQL queries and stored procedures, see Transformations in the Data Integration help.

Configuring an SQL transformation

Your mapping includes user IDs in the data flow. You want to include user names in addition to user IDs. You have a stored procedure that matches user IDs with user names in the database. You add an SQL transformation to your mapping, select the stored procedure, and map the userId incoming field with the userId input field in the stored procedure. Add an SQL transformation in a Microsoft Azure Synapse SQL mapping.
You check the Output Fields tab for the SQL transformation to confirm that it includes the username field. When you run the mapping, the username value is returned with the user ID.
Configuring a mapping with Source, Target, and SQL transformations
This example lists the tasks required to configure an SQL transformation that calls a stored procedure in Microsoft Azure Synapse SQL:
  1. 1. Enter a name and description for the SQL transformation.
  2. 2. In the Incoming Fields tab, define field rules that determine the data to include in the transformation.
  3. 3. In the Properties panel of the SQL transformation, click the SQL tab.
  4. 4. In the SQL tab, perform the following tasks:
    1. a. Select the connection to the database. You can select the connection or use a parameter.
    2. b. Set the SQL type to Stored Procedure.
    3. c. Click Select to select the stored procedure from the database, or enter the exact name of the stored procedure to call.
    4. The stored procedure name is case-sensitive.
      Note: If you add a new stored procedure to the database while you have the mapping open, the new stored procedure does not appear in the list of available stored procedures. To refresh the list, close and reopen the mapping.
  5. 5. In the Field Mapping tab, specify how to map incoming fields to the input fields of the selected stored procedure.
  6. 6. Define advanced properties for the transformation according to your requirement.
To configure an SQL transformation using the SQL type as SQL entered query, see Transformations in the Data Integration help.

Rules and guidelines for SQL transformations

Consider the following rules and guidelines when you configure an SQL transformation in mappings: