Pass widget parameter to T-SQL query in Fabric

[Sperling]

Lets say my first cell in my Fabric notebook is:

%%pyspark

import ipywidgets as widgets

ADF_PipelineRunId= widgets.Text(description = "ADF_PipelineRunId", value = "00000000-0000-0000-0000-000000000000")

display(ADF_PipelineRunId)

I've toggled the cell to be a parameter cell.

How do I then select the input in a T-SQL cell?

In Databricks the following works, but I can't find a workaround in Fabric:

SELECT "${ADF_PipelineRunId}" AS ADF_PipelineRunId

[Anonymous · Accepted solution]

Hi @Sperling 

You can try a two-step approach:

1. Capture the Widget Value in PySpark: First, ensure you capture the value from the widget in a PySpark cell. You've already done this part.

   import ipywidgets as widgets
   ADF_PipelineRunId = widgets.Text(description="ADF_PipelineRunId", value="00000000-0000-0000-0000-000000000000")
   display(ADF_PipelineRunId)
   

2. Pass the Value to a T-SQL Query: Next, you'll need to use PySpark to dynamically construct your T-SQL query string with the widget's value included. This can be done by creating a query string in PySpark and then executing it using the Spark SQL context.

   Here's how you can do it:

   # Assuming ADF_PipelineRunId.value holds the value you want to pass to your T-SQL query
   query = f"""
   SELECT '{ADF_PipelineRunId.value}' AS ADF_PipelineRunId
   """
   spark.sql(query)
   

   This approach involves constructing the SQL query as a string in PySpark, where you can dynamically insert the widget's value. Then, you execute this query using Spark SQL (spark.sql). This way, you can pass parameters from PySpark widgets to your SQL context.

If this doesn't work, you can also seek help at the Synapse forum: Get Help with Synapse

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Sperling]

Hi @Anonymous 

I found a solution with your approach. I'm combining it with data from another source, but I just made my initial query in python and added the parameters, and then continued with T-SQL using the spark.sql query.

Thanks for the help!