Unable to create functions in Fabric Data Warehouse

[JE_test]

Hi!

I was not sure in which forum this question fits, but I ended up here.

Has anyone here tried to create a function in Fabric Data Warehouse? I don't have much experience creating functions in SQL, so maybe I'm doing something wrong. But I'm not able to create either a scalar or table valued function, i always get the error: 

RETURNS @return_variable is not supported for CREATE/ALTER FUNCTION.

Is it even possible to create functions today i Fabric Data Warehouse? If so, what is the syntax? and what RETURN types are supported?

[Anonymous · Accepted solution]

Hi @JE_test 
Apologies for the issue you have been facing.
When I tried to create a simple function without any parameters, the function was created successfully.

But scalar functions are not yet available in Fabric. For more information please refer to this link: Link1

Hope this helps. Please let us know if you have any further questions.

[JE_test]

It seems like the syntax is slightly different if you declare variables inside the function. I tried to create a very simple function and still got the same error:

CREATE FUNCTION meta.testfunction (@TestID int)
returns varchar(200)
as
begin
declare  varchar(200)
select  = Namn from [datastore].[test]
where TestID = @TestID
return 
end

Msg 15869, Level 16, State 55, Procedure testfunction, Line 13 [Batch Start Line 0]
RETURNS <return_data_type> is not supported for CREATE/ALTER FUNCTION.

What return-values are even supported?

Edit: I forgot that I read that scalar functions are not yet supported... https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-sql-data-warehouse?view=aps-p...

[Anonymous]

Hi @JE_test 
Are you still facing this issue? If yes will respond back with the more details and we will try to help.
Thanks

[JE_test]

Yes I still have the issue. Here is a simple function that gives an error, which is an extremely dumbed down version of what in trying to do. (take in a variable and return a date from another table).

Dummy-function:

CREATE FUNCTION [datastore].[fnDummy]
(@dummyID int)
RETURNS @T TABLE(N date)
AS
BEGIN
    DECLARE @NOW DATETIME
    SET @NOW = GETDATE()
	INSERT INTO @T SELECT @NOW
    RETURN 
END
GO

Error:

Msg 15869, Level 16, State 54, Procedure fnDummy, Line 24 [Batch Start Line 0]
RETURNS @return_variable is not supported for CREATE/ALTER FUNCTION.

[Anonymous · Accepted solution]

Hi @JE_test 
Apologies for the issue you have been facing.
When I tried to create a simple function without any parameters, the function was created successfully.

But scalar functions are not yet available in Fabric. For more information please refer to this link: Link1

Hope this helps. Please let us know if you have any further questions.

[JE_test · Accepted solution]

Thanks for the additional examples.

I'm copying a function from a local SQL server, and it is a scalar function with variables.

But my workaround includes CTEs instead of variables, and returning a table with only one entry. I think it will work.

[fabricator1]

In this post, there is an example of a SQL Function which worked