Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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.
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.
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...
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
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.
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.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
3 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
3 | |
2 | |
1 | |
1 | |
1 |