CODEWITHSUNDEEP
functionazure-synapse
JJK
JJK

Reputation: 25

How can I get around the error "SELECT statement is not allowed in user-defined functions" in Synapse (Azure Datawarehouse)?

I have an Azure Synapse Data Warehouse running on a dedicated SQL Pool so it can store tables and do analysis. I have a function that works in Azure SQL and I copied it to my Synapse warehouse. It returns either 'yes' or 'no' as a string depending on whether or not the value we are feeding in appears in the table - the function is copied below

create function [dbo].[fnSourceTableExists] (
    @SchemaName sysname,
    @TableName sysname
)
returns nvarchar(3)

as
begin
    return case when exists (select TABLE_NAME from [SomeSchema].[Company_Tables] where TABLE_SCHEMA = @SchemaName and @TableName = ObjectName)  then 'Yes' else 'No' end
end

It gives the error "SELECT statement is not allowed in user-defined functions" I have several functions similar to this which involve getting a single value from a select query. How can I get around this?

Upvotes: 1

Views: 2240

Answers (1)

Saideep Arikontham
Saideep Arikontham

Reputation: 6104

According to the following official Microsoft documentation, Azure synapse analytics gives an error when the body of the function contains a SELECT query.

CREATE FUNCTION (Azure Synapse Analytics) - SQL Server | Microsoft Docs

Looking at the user-defined function that you have, one viable option would have been to use Stored Procedures instead. But Stored Procedures in Azure synapse have certain drawbacks, among which one of them is that it does not support the usage of RETURN statement. Refer to the following official documentation to know about all the drawbacks of Stored Procedures in Azure synapse analytics.

Using stored procedures - Azure Synapse Analytics | Microsoft Docs

Since your user-defined functions have only a single select statement and there seems to be no proper workaround, it would be better to execute the select statements as is instead of using user-defined functions. If you have access to SQL Server Management Studio, you can use Dedicated SQL endpoint along with the SQL authentication to connect to data warehouse to SSMS and work with user-defined functions.

Upvotes: 1

Related Questions