Coefficient's SQL Parameter feature gives additional flexibility when writing custom SQL queries for your imports. Using this feature, you can dynamically reference a cell/range of cells on your spreadsheet, which automatically captures the value from the cell, allowing you to run your query without updating it manually! How cool is that, huh? 😎
*This feature is currently supported on Google Sheets only*
How to use SQL Parameters with your import?
What are SQL Parameters?
When using a custom SQL query with Coefficient, you can now add a Parameter that will dynamically point to a specific cell/range of cells/values in your sheet. This will allow you to create complex queries + Parameters that allow your teammates to enter the needed data in a cell for the import to run.
How to use SQL Parameters with your import?
SQL Parameters can be used with any Coefficient Integration that has custom SQL queries as an import option. These include MySQL, PostgreSQL, MS SQL, Snowflake, RedShift, etc.
(This example will use MySQL and set the value for the “film.release_year” column using the SQL Parameter feature. The goal is to pull a list of movies based on the value we set for cell B1 in a new tab.)
|
1. To set the SQL parameter, on a new sheet, go to cell “B1” and then enter “2006”. This value will be used in the film.release_year column. |
2. Select “Import from…” from the main menu. |
3. Select MySQL and choose “Custom SQL Query”. |
4. The Import Preview window will appear. You will see all your previously created parameters listed, but you can always add new ones.
ℹ️ NOTE: The parameters listed are other parameters in the current spreadsheet and are not shared across spreadsheets.
5. Click the “Add another parameter” button to add your new SQL Parameter. Each parameter name must be UNIQUE. You will need to manually enter the cell/range of cells referenced in your Sheet.
ℹ️ NOTE: The format of your cell/range should be ‘<Name of your sheet>’!<cell or range of cells>”. (Example: “Import1!A1” or “Import1!A1:D1”)’
6. Add your query to this import. If you are using the parameter, you MUST include “{{” before and “}}” after for it to be valid. Click the “Refresh Preview” button to see how your data is shown.
ℹ️ PRO TIP: You can start typing “{{” and a list of available variables should appear so you do not have to copy and paste it. Make sure you place “}}” at the end.
ℹ️ NOTE: The system should automatically list your parameters for easy selection once you type in “{{”.
7. Give your import a UNIQUE name, and click “Import”.
8. Coefficient should now capture the value we entered on our parameter cell. Your import now shows all the films released in 2006. 🎉 🙌
FAQs for SQL Parameters
Is there a limit to how many parameters I can create and use for my query?
You can create up to 50 parameters and use all those parameters on your SQL query. 🙌
What data types can be used when creating a parameter?
Boolean, Number, Dates, and String are the following data types that are compatible with SQL Parameters.
Can the cell/s referencing my SQL Parameter have formulas in it?
SQL Parameters can be defined on any sheet, and you can use formulas on your cell(s). Just make sure that you set the correct sheet name and range when creating it.
Can I use a range of cells with SQL Parameters?
Yes, you can use a range of cells with SQL Parameters. If you do, then it can be used in the query as a list.
Can variables be shared across spreadsheets?
Variables are scoped to the current spreadsheet and can be used across different imports in the current spreadsheet but are not supported across different spreadsheets.