[BUG]: SQL Server Test: Unsupported Functions (LEN, SET, TRY/CATCH)

[Fatiine]

Is there an existing issue for this?

• I have searched the existing issues

Category of Bug / Issue

Converter bug

Current Behavior

Description
When using the Lakebridge transpiler to convert SQL Server (T-SQL) scripts to Databricks SQL, several SQL Server-specific features are not being properly converted. This results in output code that cannot be executed directly in Databricks SQL without manual intervention.

Observed Issues
Function Conversion:
The LEN() function from SQL Server is not automatically converted to LENGTH(), which is required in Databricks SQL.

Variable Assignment:
Variable assignments using SET variable = value; are not converted to the Databricks SQL syntax SET VAR variable = value;.

String Concatenation:
Expressions using 'A' + 'B' for string concatenation are not converted to use CONCAT('A', 'B') or the || operator, as required by Databricks SQL.

Error Handling Blocks:
T-SQL error handling constructs such as BEGIN TRY ... END CATCH and error functions like ERROR_MESSAGE() remain in the output, even though they are not supported in Databricks SQL.

Expected Behavior

SQL Server Input	Transpiled Output (Current)	Expected Output (Databricks SQL)
LEN(column_name)	LEN(column_name)	LENGTH(column_name)
SET variable = value;	SET variable = value;	SET VAR variable = value;
'A' + 'B'	'A' + 'B'	CONCAT('A', 'B') or `'A'
BEGIN TRY ... END CATCH	BEGIN TRY ... END CATCH	(should be removed)

Steps To Reproduce

1. Obtain the SQL Server Procedure File: Download or copy the contents of the file: proc_load_silver.sql

• This file contains a full SQL Server stored procedure for loading the "silver" layer in a data warehouse, including variable declarations, error handling, string operations, and ETL logic.

2. Run the File Through the Transpiler

• Use the Lakebridge transpiler command line

3. Review the Transpiler Output

• Download or view the transpiled Databricks SQL code.

• Compare the output to the original, focusing on:

• Function conversions (e.g., LEN() to LENGTH())

• Variable assignment syntax (SET variable = ...; vs. SET VAR variable = ...;)

• String concatenation (+ vs. CONCAT() or ||)

• Error handling blocks (BEGIN TRY ... END CATCH)

Relevant log output or Exception details

Sample Query

Operating System

macOS

Version

latest via Databricks CLI