Problem with synapse pipeline parameter

Question

Hello,

I am creating a pipeline inside of Synapse and within this pipeline i integrated a SQL Stored Procedure Activity calling a Stored Activity I created.

I tried to create a parameter inside the pipeline referenced by the Stored Activity Parameter in order to change the date of a where statement based on hard coded date i include before running the pipeline.

inside of the Pipeline parameter i did : Add a parameter:

• Name: DateParam
• Type: String
• Default Value: a default value like '2024-08-06'

and then inside of the stored procedure activity i called this value by creating a parameter with the value @pipeline().parameters.DateParam

And finally i called this variable inside the SQL query like this :

but when trying to run the pipeline i kept getting this error : ErrorCode=InvalidTemplate, ErrorMessage=Unable to parse expression 'DateParam'

CREATE PROCEDURE FilterDataByDate 
@DateParam NVARCHAR(10) 
AS BEGIN 
SELECT * FROM YourTable 
WHERE CAST(YourDateColumn AS DATE) = CAST(@DateParam AS DATE) END 

Answer 1

Hi WAJIH Arfaoui,

Thanks for reaching out to Microsoft Q&A.

Ensure that the parameter is properly defined and referenced in your pipeline and activity settings. Validate the following steps to narrow down the issue.

1. Define the Pipeline Parameter:

• Go to your Synapse pipeline -->Add a pipeline parameter by going to the "Parameters" section on the pipeline canvas.
• Name the parameter 'DateParam'.
• Set the Type to 'String'.
• Enter a default value, such as '2024-08-06'.

2. Configure the Stored Procedure Activity:

• In your Stored Procedure Activity, ensure you have the correct SQL Stored Procedure that accepts a parameter. Based on your description, it seems you have this set up already.
• Go to the “Settings” tab of the Stored Procedure Activity. Under “Stored Procedure Parameters,” add a new parameter.
• Set the "Parameter Name" to match the parameter in your stored procedure (e.g., '@DateParam').
• In the "Value" field, instead of using '@pipeline().parameters.DateParam' directly in your SQL code, you should set this in the Expression field: '@pipeline().parameters.DateParam'. Make sure to select the Expression tab (fx) when setting this value.

3. Validate the Expression:

• Double-check that you have entered the expression correctly: '@pipeline().parameters.DateParam'. Make sure there are no extra spaces or syntax errors in the expression.

4. Debugging Tips:

• Before running the pipeline, try to manually set the 'DateParam' parameter in the debug/run settings to see if the pipeline executes correctly. Check the SQL code in the stored procedure to ensure there are no errors and that it correctly uses the parameter passed to it.

Try and let me know

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Answer 2

@WAJIH Arfaoui

Thanks for reaching out to Microsoft Q&A.

It seems like you're having an issue with referencing a pipeline parameter in your SQL stored procedure activity within Azure Synapse. The error message you're seeing, "Unable to parse expression 'DateParam'", is likely due to the fact that you're trying to reference the parameter directly without using the @ symbol.

In Azure Synapse, when referencing a pipeline parameter in an activity, you need to use the @ symbol followed by the parameter name. So in your case, you should reference the DateParam parameter as @DateParam instead of just DateParam.

Here's how you can modify your SQL query to correctly reference the pipeline parameter:

CREATE PROCEDURE FilterDataByDate 
    @DateParam NVARCHAR(10) 
AS BEGIN 
    SELECT * FROM YourTable 
    WHERE CAST(YourDateColumn AS DATE) = CAST(@DateParam AS DATE) 
END

And when calling this stored procedure from your Synapse pipeline, you should pass the DateParam parameter like this:

{
    "properties": {
        "activities": [
            {
                "name": "Stored Procedure Activity",
                "type": "SqlServerStoredProcedure",
                "typeProperties": {
                    "storedProcedureName": "FilterDataByDate",
                    "storedProcedureParameters": {
                        "DateParam": {
                            "value": "@pipeline().parameters.DateParam"
                        }
                    }
                },
                ...
            }
        ],
        "parameters": {
            "DateParam": {
                "type": "string",
                "defaultValue": "2024-08-06"
            }
        }
    }
}

Note that we're passing the DateParam parameter value as "value": "@pipeline().parameters.DateParam". This ensures that the value of the DateParam pipeline parameter is correctly passed to the stored procedure.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.