question

GangaPrasanthExternal-1578 avatar image
0 Votes"
GangaPrasanthExternal-1578 asked ShaikMaheer-MSFT commented

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


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.





azure-synapse-analytics
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @GangaPrasanthExternal-1578 ,

Just checking in to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hi @GangaPrasanthExternal-1578 ,

Following up to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·
ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered

Hi @GangaPrasanthExternal-1578 ,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach edited

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://docs.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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.