I am trying to create a UDF to get full_name in snowflake with the below code:
CREATE OR REPLACE FUNCTION LASTNAMEFIRST (LAST_NAME char(100),FIRST_NAME char(100))
RETURNS CHAR(1000)
AS
'declare
FULL_NAME varchar;
begin
FULL_NAME := FIRST_NAME || '' '' || LAST_NAME;
return FULL_NAME;
End';
I am getting Compilation of SQL UDF failed: SQL compilation error: syntax error line 2 at position 1 unexpected 'FULL_NAME'. error
Your help would be appreciated.
The Snowflake Scripting block is not necessary:
CREATE OR REPLACE FUNCTION LASTNAMEFIRST (LAST_NAME char(100),FIRST_NAME char(100))
RETURNS CHAR(1000)
AS
' FIRST_NAME || '' '' || LAST_NAME';
Call:
SELECT LASTNAMEFIRST('a', 'b');
-- b a
If the BEGIN/END block is required then stored procedure is the way to go:
CREATE OR REPLACE PROCEDURE LASTNAMEFIRST (LAST_NAME char(100),FIRST_NAME char(100))
RETURNS TEXT
LANGUAGE SQL
AS
$$
declare
FULL_NAME varchar;
begin
FULL_NAME := FIRST_NAME || ' ' || LAST_NAME;
return FULL_NAME;
End;
$$;
CALL LASTNAMEFIRST('a', 'b');
-- b a