How can i create scalar functions in Microsoft fabric warehouse ?

[ChristinaODT]

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]

GO

/****** 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

[ahmetyilmaz · Accepted solution]

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

[nilendraFabric]

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
);

[v-nmadadi-msft]

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.

[v-nmadadi-msft]

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.

[v-nmadadi-msft]

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