Issue using nested stored procedure calls in fabric warehouse

[Anonymous]

Hi All,

I am trying to use nested stored procedure on Fabric warehouse. It keeps failing with the below error:

The query references an object that is not supported in distributed processing mode.

However, I have not used any object like a sys table in the child stored proc. The tables used in parent stored proc and child stored proc are all valid and can be referenced when checked using individual sqls. Is calling nested stored proc supported ? 

Below is the stored proc format that I am testing :

CREATE PROC [SCHEMA].[PARENT_PROC_NAME] AS

DECLARE

@var1 Nvarchar(150)

@var2 INT

BEGIN

EXECUTE [SCHEMA].[CHILD_PROC_NAME] @var1,@var2

END

GO

[nilendraFabric]

Hello @Anonymous 

as @andrewsommer  mentioned it is a known limitation.

orchestrate the execution order of your logic using Fabric Pipelines and maintain the dependencies using if else ij pipeline 

[andrewsommer · Accepted solution]

As of current Fabric Warehouse implementation, calling a stored procedure from another stored procedure is not supported in distributed mode. This is a known limitation inherited from Synapse SQL Dedicated Pools.

• Fabric Warehouse supports single-level stored procedure execution.
• Nested calls are often flagged because execution context cannot be distributed when dynamic execution or parameter passing between procedures is involved.

Please mark this post as solution if it helps you. Appreciate Kudos.