scalar function in databricks

Shree23
New Contributor III

‎12-04-2023 12:10 PM

Hi Expert,

here is sql server scalar function how to convert in databricks function

SQL
CREATE function [dbo].[gettrans](@PickupCompany nvarchar(2),
@SupplyCountry int, @TxnSource nvarchar(10),
@locId nvarchar(50), @ExternalSiteId nvarchar(50))
RETURNS INT
AS

BEGIN
DECLARE @worldSiteid INT
SET @worldSiteid = 0

If (@TxnSource = 'CDX')
BEGIN

0 Kudos

Shree23
New Contributor III

‎12-04-2023 11:17 PM

Suggestion pls

0 Kudos

MathieuDB
Databricks Employee
Databricks Employee

‎11-22-2024 07:15 AM

Hello @Shree23 ,
In Databricks, you can create scalar or tabular functions using SQL or Python. Here is the documentation .
I converted your SQL Server function to Databricks standards.

CREATE OR REPLACE FUNCTION gettrans(
  PickupCompany STRING,
  SupplyCountry INT,
  TxnSource STRING,
  locId STRING,
  ExternalSiteId STRING
) RETURNS INT
RETURN (
  SELECT CASE
    WHEN TxnSource = 'CDX' THEN (
      -- Add business logic here
      0
    )
    ELSE 1
  END
);

SELECT gettrans('Purolator', 'CAN', 'CHI', '123', 'MAIN') as transactions;

As you can see, the syntax is pretty straightforward. The datatype can be INT, STRING, ARRAY, DECIMAL... 
The variables don't need to be prefixed by anything (@). The rest of the logic is pretty similar to what you will find in SQL Server.

Hope this helps!

 

0 Kudos