unable to create The SELECT statement is not allowed in user-defined functions in synapse analytics azure

Question

I am trying to creating a function in Azure synapse analyzing SQL but I am getting the below error
"The SELECT statement is not allowed in user-defined functions"

Please find below the function.

create FUNCTION  [dbo].[GET_YEAR]
(
    @IN_YEAR_CD VARCHAR(20)
)
RETURNS VARCHAR(4)
AS
BEGIN

DECLARE @v_yr_calc nvarchar(4000), @v_override_ind CHAR(1),@ret_yr varchar(12),@ret_override_yr varchar(4)


select @v_yr_calc=''+TARGET_YR_CALC, @v_override_ind=OVERRIDE_IND, @ret_override_yr=OVERRIDE_YR 
 from dbo.LKP_YEAR 
 where source_yr= @IN_YEAR_CD

if @v_override_ind='Y' 
RETURN @ret_override_yr
else
bEgin

 SELECT @ret_yr=  CASE WHEN @IN_YEAR_CD= 'CY' THEN YEAR(DATEADD(dd, -32, logging.getdate()))
WHEN @IN_YEAR_CD='NY' THEN YEAR(DATEADD(dd,-32, logging.getdate())) +1
WHEN @IN_YEAR_CD='PY' THEN YEAR(DATEADD(dd,-32, logging.getdate())) -1
WHEN @IN_YEAR_CD='PY-1' THEN YEAR(DATEADD(dd,-32, logging.getdate())) -2
WHEN @IN_YEAR_CD='WB' THEN YEAR(DATEADD(dd,-32, logging.getdate())) +1
WHEN @IN_YEAR_CD='WF' THEN YEAR(DATEADD(dd,-32, logging.getdate()))
END

--EXEC @ret_yr= sp_executesql @v_yr_calc
RETURN @ret_yr
END
RETURN 0

END

Please help me on mirage funcation azure SQL to synapse sql.

Answer 1

Hi @Ganga, Prasanth (External) ,

Thank you for posting query in Microsoft Q& A Platform.

In Analytics Platform System (PDW), the return value from user-defined function should be must be a scalar (single) value.

In your sample code, I observed that to set value in "@ret_yr" you are using SELECT. I think you should use SET there.

If your user-defined function is going to return table type then you are good to use SELECT there.

Could you please try using SET to set value of return parameter and let us know how it goes? Thank you.

Answer 2

Good day Ganga and welcome to the QnA forums,

I see a few issues in your code which are not related to your original question. Let's start with your question, but keep reading for better query in the future.

The SELECT statement is not allowed in user-defined functions

Yep! azure synapse does not support reference any database data, and it is not related of using SELECT inside the function or other ways.

You can see it well documented here: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-sql-data-warehouse?view=aps-pdw-2016-au7#arguments

The function_body cannot contain a SELECT statement and cannot reference database data. The function_body cannot reference tables or views.

This behavior is different then SQL Server.

Since you did not provided the DDL+DML in order to reproduce the issue, we can only give you general solution and you will need to implement it yourself - or provide the missing information if not succeed.

Optional workaround the limitation

(1) You can reference any database data (execute the select query) before you call the function and use the values as input parameters in the function.

(2) You can use CROSS APPLY if you want to use data from table(s) as input to a function which is used inline with a query.

Back to the start, and some more points

(1) Using SELECT as input to a scalar variable is huge problematic since SELECT returns a SET of records and not a single one. Most database server "know" how to by pass this poor coding by using their interpretation (for example using the first or the last row returned from the query). If this approach is a must and you know by sure that your query can return only one row then it is recommended to use "top 1" to avoid issues.

(2) Your function supposed to return AVRCHAR(4) according to your definition RETURNS VARCHAR(4), but you declares @ret_yr varchar(12) which mean that returns VARCHAR(12). Moreover! If the condition of the IF is not TRUE then you function return INTEGER RETURN 0. This make no sense.