When using Session Variables in SQL queries, the data type of the returned data must match the data type expected by the SQL query.
If necessary, the CAST function can be used to convert the Session Variable to the appropriate data type.
- Click on the “+” icon next to the “Actions” title on the left panel in the Datasource module.
- Select “New SQL Action” from the drop-down menu.
- Write your SQL query via SQL Editor. Example:
SELECT CAST(SESSION_CONTEXT(N'user_id') AS NVARCHAR(100)) AS UserName
4. Click on the “CREATE” button at the top right.
This method ensures that the Session Variable is processed with the correct data type.
Testing SQL Action
After defining SQL Action and Session Variables, test your query:
- Run the SQL Action by clicking on the “Test” button on the right sidebar.
- Make sure that the query returns correct results based on the values from the Session Variable.
- Verify that the Session Variable values are dynamically passed to the SQL query.