Anyone here had luck with creating and using UDFs on Azure data warehouse database? I am in the middle of migrating an on-prem warehouse from SQL Server 2014 to Azure datawarehouse and I ran into an issue with UDFs.
CREATE FUNCTION dbo.fn_GetImpliedRate (@Multiple float, @term int)
RETURNS float
AS
BEGIN
DECLARE @ImpInt float
IF(@Term = 1)
SET @ImpInt = (select [1] from dbo.ImpliedRate where Multiple = @Multiple);
IF(@Term = 2)
SET @ImpInt = (select [2] from dbo.ImpliedRate where Multiple = @Multiple);
IF(@Term = 3)
SET @ImpInt = (select [3] from dbo.ImpliedRate where Multiple = @Multiple);
IF(@Term = 4)
SET @ImpInt = (select [4] from dbo.ImpliedRate where Multiple = @Multiple);
RETURN @ImpInt
END;
GO
This UDF works perfectly on SQL Server 2014. When I create this on Azure data warehouse, it gets created but it doesn't work when I query it. It returns a NULL. I have verified obvious things like whether the target table exists etc. All check. I looked at CREATE FUNCTION documentation for Azure data warehouse and it has an example UDF that converts int to a decimal. This works flawlessly on Azure DW. The moment I write a simple function that has a select, it fails. Unfortunately Azure's documentation here is not really helpful and I was wondering if any of you ran into this issue. If yes, how did you resolve?
EDIT - I just tested another use case and it also doesn't work as well.:
CREATE function [dbo].[fn_GetNumberBusinessDays]
(
@StartDate datetime,
@EndDate Datetime
)
returns int
as
begin
DECLARE @NDAYS INT = 0
SELECT @NDAYS =
ISNULL( (DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) - 1 ,0) + 1
SELECT @NDAYS = @NDAYS - COUNT(*)
FROM dbo.FedHolidays
WHERE DateOfHoliday BETWEEN @StartDate AND @EndDate
RETURN @NDAYS
end
GO
