topic Re: How can i create scalar functions in Microsoft fabric warehouse ? in Data Warehouse

How can i create scalar functions in Microsoft fabric warehouse ?

ChristinaODT — Thu, 06 Mar 2025 10:40:08 GMT

I am trying to create one but getting issue - RETURNS is not supported for CREATE/ALTER FUNCTION.. Th function looks like below -

USE [TBPP_AFTC]

/****** Object: UserDefinedFunction

[dbo].[ROUND_MNRF] Script Date: 28-10-2024 17:06:40 ******/

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE FUNCTION [dbo].[ROUND_MNRF] (

VALUEfloat(53), @PRECISION float(53) )

RETURNS float(53) AS

BEGIN

DECLARE @A numeric(18, 9), @B numeric(18, 9), @C numeric(18, 9), @D numeric(18, 9), @T numeric(18, 9), Val numeric(18, 9), @RESULT numeric(18, 9)

SET @A = CAST('0.' + RIGHT(REPLICATE('0', @PRECISION) + '1', @PRECISION) AS DECIMAL(38, 10))

SET @C = 1 / @A

SET Val = VALUE

SET @T = ROUND(@VAL,0,1) -- Truncate value

SET Val = Val - @T

SET @B = (@VAL * @C) / (@A * @C)

SET @D = @B - ROUND(@B,0,1)

SET @B = ROUND(@B,0,1)

IF (@D > 0.5) SET @B = @B + 1 IF (@D = 0.5) BEGIN IF (@B % 2) = 1 SET @B = @B + 1 END SET @RESULT = @T + @A * ROUND(@B,0,1) RETURN @RESULT END GO

Re: How can i create scalar functions in Microsoft fabric warehouse ?

ahmetyilmaz — Thu, 06 Mar 2025 11:32:27 GMT

Hi, Scalar function is not available in Microsoft Fabric Warehouse. Returning a single row table instead will work fine.

Re: How can i create scalar functions in Microsoft fabric warehouse ?

nilendraFabric — Thu, 06 Mar 2025 20:04:54 GMT

as ahmetyilmaz mentioned

Creating scalar user-defined functions (UDFs) in Microsoft Fabric Warehouse is not currently supported.

Fabric Warehouse only allows inline table-valued functions (TVFs)
Variable declarations (`DECLARE`) and multi-statement logic (`BEGIN/END`) in functions are unsupported

Convert your scalar function logic to return a single-row table

Give it a try

CREATE FUNCTION [dbo].[ROUND_MNRF_TVF] (
@VALUE float(53),
@PRECISION float(53)
)
RETURNS TABLE
AS
RETURN (
SELECT
CAST(
ROUND(@VALUE, @PRECISION)
AS float(53)
) AS RoundedValue
);

Re: How can i create scalar functions in Microsoft fabric warehouse ?

Anonymous — Mon, 10 Mar 2025 11:48:31 GMT

Hi ChristinaODT,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: How can i create scalar functions in Microsoft fabric warehouse ?

Anonymous — Thu, 13 Mar 2025 10:22:59 GMT

Hi ChristinaODT,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: How can i create scalar functions in Microsoft fabric warehouse ?

Anonymous — Mon, 17 Mar 2025 04:05:41 GMT

Hi ChristinaODT,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards