SQLAnywhere: Failure on creation of procedure

Question

I wanted to create a procedure like this:

CREATE OR REPLACE PROCEDURE __generate_new_VSN(@oldVSN varchar(100)) 
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN '1'
END

but getting when running it via isql:

Anweisung konnte nicht ausgeführt werden.
Syntaxfehler bei 'RETURNS' in Zeile 2
SQLCODE=-131, ODBC 3-Status="42000"
Zeile 1, Spalte 1

which can be translated to something like

Command couldn't be executed
Syntax error at 'RETURNS' on line 2
SQLCODE=-131, ODBC 3-Status="42000"
Zeile 1, Spalte 1

What did I wrong?

score 2 · Accepted Answer · answered Aug 05 '14 at 09:03

2

The problem is you are mixing the CREATE FUNCTION syntax with that for CREATE PROCEDURE

This should work:

CREATE OR REPLACE PROCEDURE __generate_new_VSN(@oldVSN varchar(100), OUT @newVSN  varchar(100)) 
BEGIN
      RETURN '1'
END

the full syntax is here

answered Aug 05 '14 at 09:03

Justin W

This did the trick -- thanks. Weird is, that similar examples did work. – frlan Aug 05 '14 at 09:17
If you want a result set you can use a "RESULT" clause - you aren't thinking of that by any chance eg CREATE OR REPLACE PROCEDURE __generate_new_VSN(@oldVSN varchar(100)) RESULT (@newVSN varchar(100)) BEGIN RETURN '1' END – Justin W Aug 05 '14 at 09:38
Ahh.... Found the issue at all: Was a funny mixup of procedure and functions .. Just solved it by cleaning up a little. Thanks for the input! – frlan Aug 05 '14 at 13:25

1 Answers1