Named procedure parameters

Emulation context

In SQL Server, stored procedures can be invoked using named parameters, for example:

EXEC dbo.MyProc @Param1 = 42, @Param2 = 'abc'

This improves readability, allows reordering of arguments, and reduces the risk of positional mismatches.

However, Microsoft Fabric Warehouse does not support named parameters in procedure calls. Instead, it requires strictly positional arguments, for example:

EXEC dbo.MyProc 42, 'abc'

This difference creates an important compatibility gap:

• SQL Server procedures can rely on the caller using named arguments, allowing the definition and call order to differ.

• Fabric Warehouse mandates that procedure calls match the exact declared parameter order.

This mismatch affects any migrated scripts, applications, or orchestration tools that depend on the flexibility of named parameters.

---

Emulation strategy

SQL Tran addresses this by transforming named EXEC calls into positional EXEC calls by:

• Analyzing the stored procedure signature to determine the declared parameter order.

• Rewriting any named parameter calls into positional calls, placing each argument in the correct slot. Even if the original SQL call provided parameters out of order using names, the translated Fabric call aligns the arguments correctly by position.

• Inserting explicit NULL for any omitted optional parameters to maintain positional integrity.

• Adding SET statements using ISNULL inside the target procedure body to apply declared default values at runtime.

• Including /* SQLTRAN FIX */ comments alongside the transformed calls to show the original named expressions for traceability.

This automatic transformation enables most standard EXEC calls to continue functioning correctly in Fabric Warehouse without manual rewrites.

---

Code example

SQL Server:

CREATE PROCEDURE dbo.ExampleProcedure
    @PersonID INT,
    @FirstName NVARCHAR(50),
    @Height DECIMAL(5,2) = 0.0,
    @Age INT = NULL
AS
BEGIN
    RETURN;
END
GO

CREATE PROCEDURE dbo.TestNamedParameters
AS
BEGIN
    -- Call with reordered parameters
    EXEC dbo.ExampleProcedure
        @Age = 30,
        @FirstName = 'John',        
        @PersonID = 12345
END;

Fabric Warehouse (generated by SQL Tran):

CREATE PROCEDURE dbo.ExampleProcedure
    @PersonID INT,
    @FirstName NVARCHAR(50),
    @Height DECIMAL(5,2)/* SQLTRAN FIX:  = 0.0 */,
    @Age INT/* SQLTRAN FIX:  = NULL */
AS
BEGIN
    SET @Height = ISNULL(@Height, 0.0);
    SET @Age = ISNULL(@Age, NULL);
    /* SQLTRAN FIX: RETURN; */
END
GO
CREATE PROCEDURE dbo.TestNamedParameters
AS
BEGIN
    -- Call with reordered parameters
    EXEC dbo.ExampleProcedure 12345,
	'John',
	NULL,
	30; /* SQLTRAN FIX: EXEC dbo.ExampleProcedure
        @Age = 30,
        @FirstName = 'John',        
        @PersonID = 12345 */
END;

Named procedure parameters emulation in SQL Tran

---

Important notes

• Procedure signature dependency: The translation relies on having access to the procedure definition to resolve parameter order. Missing or outdated metadata can result in incorrect rewrites.

• Explicit NULL handling: When an optional parameter is omitted, SQL Tran inserts NULL into the positional call, even if the procedure defines another default. The translated procedure applies the declared defaults using SET and ISNULL.

• Dynamic EXEC exclusion: Dynamically constructed EXEC statements (via strings, variables, or sp_executesql) are not transformed by SQL Tran and require manual handling.

• Manual validation recommended: For dynamic, indirect, or complex procedures, human review is essential to ensure that the translation aligns with the intended semantics and preserves default behaviors.