Stored Procedure to Manipulate Tables

[BriefStop]

I am migrating stored procedures from SSIS into a Fabric Warehouse stored procedure. When I try to run it from a script or stored procedure activity in a pipeline I get the error "queries referencing variables are not supported in distributed processing mode" similar to this article https://community.fabric.microsoft.com/t5/Data-Warehouse/SQL-statement-in-Warehouse/m-p/3777309 

My stored procedure contains these things which I think is causing the error:

• Input variables
• Insert rows into an existing table
• Delete rows from an existing table

What I've tried

• I tried to run through a Lookup activity similar to this article(https://community.fabric.microsoft.com/t5/Pipelines/How-to-Pass-Parameters-to-a-Stored-Procedure-in-...) but am getting the same error code.
• Run through Stored Procedure activity - No luck
• Run through Notebook - Use Fabric tokens to connect to the warehouse directly as a database
  • Seems to run?? I need to keep testing

Is a Notebook really the only way to do this? Ideally I was hoping for a low code option as the eventual users prefer low code

[v-sgandrathi]

Hi @BriefStop,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

Thank you.

[v-sgandrathi]

Hi @BriefStop,

Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help. 

Thank you.

[v-sgandrathi · Accepted solution]

Hi @BriefStop,

Yes, this is currently a limitation of the Fabric Warehouse engine, not an issue with your implementation.

Fabric Warehouse does not fully support variable-based predicates in distributed DML operations like DELETE, UPDATE, or certain INSERT  SELECT patterns, which causes the error regardless of whether the procedure is triggered from a Stored Procedure activity, Script activity, or Lookup activity.

For large migrated SSIS procedures, rewriting every statement into dynamic SQL is challenging and can be hard to maintain. Common workarounds include:

• Using dynamic SQL for the affected DML sections
• Moving complex logic into a Notebook/Spark flow
• Refactoring logic into set-based operations to avoid variable references in distributed execution

Your expectation is valid, this is a product limitation rather than a design issue. Microsoft is working to improve Fabric Warehouse, so this may change as more SSIS/T-SQL migration scenarios are supported.

You might also consider raising or upvoting this in the Fabric Ideas forum, as procedural T-SQL parity is a frequent migration need.

Here are the MS Docs for your reference:

Performance Guidelines - Microsoft Fabric | Microsoft Learn
CREATE TABLE AS SELECT (Microsoft Fabric and Azure Synapse Analytics) - SQL Server | Microsoft Learn
Serverless SQL pool self-help - Azure Synapse Analytics | Microsoft Learn

Thank you.

[Shai_Karmani]

The error is from inside the proc, not the activity , that's why Stored Procedure, Lookup, and Script all fail the same way. Fabric Warehouse pushes bulk DML to distributed compute nodes that can't see T-SQL variables. So DELETE … WHERE id = @ID breaks.

Try to  wrap the DML in dynamic SQL so the engine sees literals.

e.g:

DECLARE @sql NVARCHAR(MAX);
DECLARE @bid NVARCHAR(20) = CAST(@batch_id AS NVARCHAR(20));

SET @sql = N'DELETE FROM dbo.target WHERE batch_id = ' + @bid + N';';
EXEC(@sql);

Cast everything to string, single-quote dates/strings (ISO format), and only the DML needs to be dynamic.

Then retry calling it 

Skip the notebook.

If this works for you, kindly mark it as the solution and give a thumbs up.

Best,
Shai Karmani

Let's connect in LinkedIn

[BriefStop]

Got it I understand, but both options are equally insane and difficult to reuse(There are 3000 lines of SQL in this stored procedure so wrapping everything in a set will become quite tedious). Is this really the only way to run stored procedures with variables? Is this a product limitation that Microsoft will resolve eventually?