topic Re: databricks sql create function - input table name as parameter and returns complete table. in Data Engineering

databricks sql create function - input table name as parameter and returns complete table.

saurabh_aher — Mon, 18 Aug 2025 07:41:36 GMT

hi ,
I am trying to create a databricks sql unity catalog function which will take table_name as input parameter and returns the full table as output. I am getting error, kindly help

CREATE OR REPLACE FUNCTION catalog.schema.get_table( table_name STRING)

RETURNS TABLE

RETURN

(
select * from IDENTIFIER('catalog.schema.' || get_table.table_name)
)

error ---
[NOT_A_CONSTANT_STRING.NOT_CONSTANT] The expression concat('kdp_bronze_prod.salesforce.', outer(get_table.user_id)) used for the routine or clause IDENTIFIER must be a constant STRING which is NOT NULL. To be considered constant the expression must not depend on any columns, contain a subquery, or invoke a non deterministic function such as rand(). SQLSTATE: 42601

Re: databricks sql create function - input table name as parameter and returns complete table.

BS_THE_ANALYST — Mon, 18 Aug 2025 08:29:30 GMT

@saurabh_aher if you're referring to an object i.e. a table I think you should be using backticks `` and not single quotes ''.

However, based on the error message: concat('kdp_bronze_prod.salesforce.', outer(get_table.user_id)).
I think in the concat function, it often helps to wrap a string in a LIT function.

All the best,
BS

Re: databricks sql create function - input table name as parameter and returns complete table.

szymon_dybczak — Mon, 18 Aug 2025 08:35:17 GMT

Hi @saurabh_aher ,

It looks like this is a limitation of identifier clause. The identifier requires a constant STRING expression. When you're passing table_name as an input of that function it's no longer considered as a constant (known at complied time), but rather runtime value.

Re: databricks sql create function - input table name as parameter and returns complete table.

saurabh_aher — Mon, 18 Aug 2025 09:02:56 GMT

any workaround for this ??

Re: databricks sql create function - input table name as parameter and returns complete table.

szymon_dybczak — Mon, 18 Aug 2025 09:19:32 GMT

Unfortunately, if we're talking about databricks sql functions I don't know workaround.

Re: databricks sql create function - input table name as parameter and returns complete table.

ilir_nuredini — Mon, 18 Aug 2025 09:24:46 GMT

Hello @saurabh_aher ,

As a workaround maybe you can use SQL procedures, a quick example I created:

CREATE OR REPLACE PROCEDURE workspace.default.get_table_proc(IN p_fqn STRING) LANGUAGE SQL SQL SECURITY INVOKER AS BEGIN DECLARE sql_str STRING; SET sql_str = CONCAT('SELECT * FROM ', p_fqn); EXECUTE IMMEDIATE sql_str; END;

And then you can call it like this:

CALL workspace.default.get_table_proc('workspace.default.sample_trips_test');

Hope that helps a bit.

Best, Ilir

Re: databricks sql create function - input table name as parameter and returns complete table.

szymon_dybczak — Mon, 18 Aug 2025 10:00:53 GMT

Yep, that will work. Now, if that's okay with you @saurabh_aher that can be solution. If you want to stick to using databricks sql function then what I wrote before still applies.

"Unfortunately, if we're talking about databricks sql functions I don't know workaround. "

Re: databricks sql create function - input table name as parameter and returns complete table.

BS_THE_ANALYST — Mon, 18 Aug 2025 11:31:25 GMT

@szymon_dybczak I admire that you always find the appropriate information in the documentation. I will try my best to emulate this behaviour with other posts 👍.

@saurabh_aher great workaround suggestion with a stored procedure.

Lots of lessons learned here, for me, thanks guys 👏

All the best,
BS

Re: databricks sql create function - input table name as parameter and returns complete table.

szymon_dybczak — Mon, 18 Aug 2025 12:13:54 GMT

Thanks for kind words @BS_THE_ANALYST . I think you're doing really well so far and I remember your first post on community (Zero to Hero). Actually, it's really impressive how quickly you became proficient with databricks and valuable member of community.