How to translate Sybase SQL to HSQLDB dialect?

Question

I'd like to translate this very simple stored procedure from the Sybase SQL dialect to the HSQLDB dialect:

CREATE PROCEDURE dbo.some_proc 
AS
BEGIN
SELECT int_param1 FROM control WHERE id_param = "SOME_PARAM" AND id_active = "Y"
END

In my humble opinion the documentation for HSQLDB is very mysterious about stored procedures. I couldn't find the right way to do it. I'm using HSQLDB version 2.3.1.

score 0 · Answer 1 · answered Jan 16 '17 at 20:17

This can be expressed as a FUNCTION in HSQLDB

CREATE FUNCTION some_proc () RETURNS TABLE(VAL INT)
READS SQL DATA
BEGIN ATOMIC
RETURN TABLE(SELECT int_param1 FROM control WHERE id_param = 'SOME_PARAM' AND id_active = 'Y');
END

The function returns a single row table with all the values.

If your procedure always returns a single value, the declaration can be different to reflect this:

CREATE FUNCTION some_proc2 () RETURNS INT
READS SQL DATA
BEGIN ATOMIC
RETURN (SELECT int_param1 FROM control WHERE id_param = 'SOME_PARAM' AND id_active = 'Y');
END

How to translate Sybase SQL to HSQLDB dialect?

1 Answers1