SQL Stored Procedure in Databricks

[elgeo]

Hello. Is there an equivalent of SQL stored procedure in Databricks? Please note that I need a procedure that allows DML statements and not only Select statement as a function provides.

Thank you in advance

[elgeo]

Hello @Kaniz Fatma​. Thank you for your prompt reply. I have already read these articles. However my question is if there is a way to create an SP in Databricks and not call one through a jdbc connection. Thanks again

[Chris_Shehu]

Any updates on this? Are SPROC like features expected in the future? Or do stored SQL queries have all the same functionality? I know we can call those remotely. Thanks!

[Meghala]

Thanks it's also helpfull to me​

[know-how]

Is there a way to query Databricks using Power BI, via a stored procedure or similar? In my use case the query will have multiple parameters, that the user will provide values for at runtime. I don't think notebooks can be consumed directly from Power BI, but I could be wrong.

Thanks for any help on this.

[Biswajit]

I have recently went through a video link from databricks which says its possible. But, when I tried it, it did not worked.
https://www.youtube.com/watch?v=f4TxNBfSNqM

Was anyone able to create stored procedure in databricks ?

[twole]

This feature is currently in Private Preview. Please reach out to your account team and they can sign you up for it.

[smpa01]

@twole do you know when this can be generally available? 

[Andolina1]

Looks like Stored procedures are in Public preview now. But when I am trying to create a procedure as per Databricks documentaion, it still doesn't seem to work.

https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-syntax-ddl-create-pro...

can anyone help?

sample code:

CREATE PROCEDURE print_hello()
LANGUAGE SQL
SQL SECURITY INVOKER
AS BEGIN
SELECT 'Hello!';
END;

Error:

Appreciate your help!!

Thanks,

Andolina

[jameshughes]

@Andolina1 - As others mentioned, only available in DBR 17.0 which is currently in Beta in Azure.  I received the same error as you during testing and changing the cluster runtime will address it.  Probably a little early to start using at scale until 17.0 moves out of Beta.

[nikhilj0421]

Hi @Andolina1 this is supported in DBR 17.0 and above. 

[nikhilj0421]

I am able to create one with the 17.0 beta DBR version. Please refer to this: https://docs.databricks.com/aws/en/release-notes/runtime/17.0#sql-procedure-support

[sridharplv]

Its working for me without any issues if we create a cluster with DBR 17.0 

https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-create-procedure