2

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?

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

  • This did the trick -- thanks. Weird is, that similar examples did work. – frlan Aug 5 '14 at 9: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 5 '14 at 9: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 5 '14 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.