Forum Discussion

JE_test's avatar
JE_test
Icon for Resolver I rankResolver I
2 years ago
Solved

Unable to create functions in Fabric Data Warehouse

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?

data warehouse
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    JE_test
    2 years ago

    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.

6 Replies

  • JE_test's avatar
    JE_test
    Icon for Resolver I rankResolver I
    2 years ago

    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-pdw-2016-au7

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        JE_test
        Icon for Resolver I rankResolver I
        2 years ago

        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.