Question Re: Inline stored procedure/function in a query in Technology Q&A

Inline stored procedure/function in a query

Former Member — Thu, 30 Mar 2017 09:57:10 GMT

Hello, I want to write a query used on several versions of a database. The later versions have defined a stored procedure/function that I can use, (but it is missing in the earlier versions). If it does not exist, is it possible to copy/paste the code into the query and use it directly?

Here is the abbreviated stored function:

ALTER FUNCTION "AdminGroup"."MakeInternalId" (IN recordId INTEGER, IN createdTimeStamp INTEGER)
RETURNS VARCHAR(65)
DETERMINISTIC 
BEGIN
    DECLARE id VARCHAR(65); 
    DECLARE highest INTEGER;
    DECLARE prefix VARCHAR(65);
    DECLARE byteOffset INTEGER;

    SET highest = 0;

    SET byteOffset = (2 * 4) + 1;

    SELECT CAST(CAST(STRING(
       BYTE_SUBSTR(CAST(blob AS VARCHAR), byteOffset + 3, 1),
       BYTE_SUBSTR(CAST(blob AS VARCHAR), byteOffset + 2, 1),
       BYTE_SUBSTR(CAST(blob AS VARCHAR), byteOffset + 1, 1),
       BYTE_SUBSTR(CAST(blob AS VARCHAR), byteOffset, 1)) AS BINARY) AS INTEGER ) INTO highest
    FROM metadata
    WHERE  external_key = 'highest';

    SET highest = ISNULL (highest, 0);

    IF (recordId <= highest) THEN
       SET prefix = INTTOHEX ((recordId * power(2, 16)));
    ELSE
       SET prefix = INTTOHEX (0x80000000 | recordId); 
    END IF;

    SET prefix = UPPER(REPLACE(LTRIM(REPLACE(prefix,'0',' ')),' ','0'));    
    SET id = STRING (prefix, '-', createdTimeStamp);
    RETURN id;
END

I tried copy/pasting the above code into Sybase Central - Interactive SQL window, just above my query and got a syntax error where my query starts.

Thanks, David

Re: Inline stored procedure/function in a query

VolkerBarth — Thu, 30 Mar 2017 10:32:22 GMT

Is it possible to copy/paste the code into the query and use it directly?

No, AFAIK a query cannot contain a compound statement or control statements, it can only directly call stored functions and select from stored procedures.

Some alternatives:

Use expressions like the CASE or IF expression (which are different from the CASE or IF statements!) to define some conditional, expresssion-based logic which is allowed in queries
Use a temporary function
Check whether the (permanent) function exists, and create it if not (see your other question...)
Use CREATE OR REPLACE function as a handy alternative.

If your actual issue has to do with a builtin function that only exists in newer version of the database software, you could write a wrapper function that

either calls the builtin function (if that is available)
or calls a homebrown one.

(I'm not sure whether "older version of the database" refers to different versions of SQL Anywhere or different versions of your database schema...)

Re: Inline stored procedure/function in a query

Former Member — Thu, 30 Mar 2017 10:32:33 GMT

Does it work if you simply change the word "ALTER" into "CREATE"? Depending upon the versions involved, an if "CREATE OR REPLACE PROCEDURE ... " syntax is supported you could also resort to that.

Or is your issue some else like ... Maybe pasting a query with the BYTE_SUBSTR( ) into a pre-8 version of the software?

Re: Inline stored procedure/function in a query

Former Member — Thu, 30 Mar 2017 11:02:31 GMT

Let me guess: Syntax error near (the first keyword of your next statement).

Did you remember to add a semicolon after the END? That's a Watcom-SQL dialect procedure. When working in Watcom-SQL, semi-colons are required between each statement.

Re: Inline stored procedure/function in a query

Former Member — Fri, 31 Mar 2017 10:08:02 GMT

Thank you Nick, changing ALTER to CREATE TEMPORARY was exactly what I needed!

Re: Inline stored procedure/function in a query

Former Member — Fri, 31 Mar 2017 10:09:01 GMT

Thank you Volker - yes, I did use CREATE TEMPORARY. The SQL Anywhere version is sufficient, it was my database scehema that had changed to include the stored function in later versions.

Re: Inline stored procedure/function in a query

Former Member — Fri, 31 Mar 2017 10:10:22 GMT

Thank you nmelion - after adding the semicolons (and defining the function at the top as CREATE TEMPORARY), my query worked great, calling the temporary function and returning data. 🙂