Fabric User Data Function parameter defaults?

[MangoMagic]

Was trying to create a function which runs a stored proc and returns some results, is there a way to set default values in parameters, e.g. to make parameters optional and default them if no value is given?

I tried following but it doesn't seem to work, the function doesn't even publish when I try and set default values 😞

@udf.connection(argName="sqlDB",alias="Control")
@udf.function()
def GetWatermark(sqlDB: fn.FabricSqlConnection, ID: int, option: str = "Get watermark", description: str = None)-> list:
    option = option.lower()
    
    if option == "get watermark":
        query = "exec exec [dbo].[Get_ETLSettings] @ID = {ID}, @Option = '{option}';"

    #if option == "hour":

    # Establish a connection to the SQL database
    connection = sqlDB.connect()
    cursor = connection.cursor()

    # Execute the query
    cursor.execute(query)

    # Fetch all results
    results = []
    for row in cursor.fetchall():
        results.append(row)

    # Close the connection
    cursor.close()
    connection.close()
        
    return results

[v-karpurapud · Accepted solution]

Hi @MangoMagic 

Thank you for contacting the Microsoft Fabric Community Forum.

Currently, Microsoft Fabric's Python User Defined Functions (UDFs) created with the @udf.function decorator do not allow default values or optional parameters in their definitions. If you try to set a default value for a parameter, such as option: str = "Get watermark", the function will not publish or compile correctly in the Fabric environment.

To avoid issues, make sure all parameters are explicitly defined and provided when calling the function. If you need default behavior, handle it inside the function by checking if a parameter is empty or null and then assigning a value as needed. This method works within the platform's current limitations and ensures your UDFs will run as expected.

from microsoft.fabric import udf

import microsoft.fabric.functions as fn



@udf.connection(argName="sqlDB", alias="Control")

@udf.function()

def GetWatermark(sqlDB: fn.FabricSqlConnection, ID: int, option: str, description: str) -> list:

   

    if not option:

        option = "get watermark"

    else:

        option = option.lower()



    if option == "get watermark":

        query = f"EXEC [dbo].[Get_ETLSettings] @ID = {ID}, @Option = '{option}';"

    else:

      

        query = f"EXEC [dbo].[Get_ETLSettings] @ID = {ID}, @Option = '{option}';"



    connection = sqlDB.connect()

    cursor = connection.cursor()



    cursor.execute(query)

    results = cursor.fetchall()



    cursor.close()

    connection.close()



    return results

Regards,

Karpurapu D,

Microsoft Fabric Community Support Team.

[MangoMagic]

Thank you, that worked 😃