I am able to test this code individually in worksheet and I am able to compile the procedure also, but Im not able to run it without errors. Its failing at the statement where it gets the RESULT_SCAN values to variables.
CREATE OR REPLACE PROCEDURE merge_source_to_target()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER -- Enables access to session variables
AS
$$
DECLARE
v_merge_query_id VARCHAR;
v_inserts INTEGER := 0;
v_updates INTEGER := 0;
BEGIN
-- Perform the MERGE
MERGE INTO target_ns AS t
USING source_ns AS s
ON t.ID = s.ID
WHEN MATCHED AND t.value != s.value THEN
UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN
INSERT (ID, value) VALUES (s.ID, s.value);
-- Capture MERGE query ID AFTER the MERGE statement
LET v_merge_query_id := LAST_QUERY_ID();
-- Get counts using RESULT_SCAN
LET v_updates := (SELECT rows_updated FROM TABLE(RESULT_SCAN(v_merge_query_id)));
LET v_inserts := (SELECT rows_inserted FROM TABLE(RESULT_SCAN(v_merge_query_id)));
-- Return the result
RETURN 'Merge completed: ' || v_updates || ' updates, ' || v_inserts || ' inserts.';
EXCEPTION
WHEN OTHER THEN
RETURN 'Error: ' || SQLERRM;
END;
$$;