0

How to declare variable in stored procedure. I tried with the following code as in MS Sql Server.

CREATE PROCEDURE TN AS BEGIN DECLARE kkk INTEGER; SET KKK=5; IF(kkk<10) SELECT * FROM XXXX; ELSE SELECT * FROM XXXX WHERE YYYY ='French'; END;

But I am getting an error "DDL and DML can't be applied together"

Cenoy
  • 21
  • 1

1 Answers1

1

Variables can only be declared in Java stored procedures. In VoltDB, all complex stored procedures, like the one you are showing here, must be done in Java.

An example of what you want could be something like this:

public class TN extends VoltProcedure {

    int kkk = 5;

    public final SQLStmt sql1 = new SQLStmt(
        "SELECT * FROM XXXX;");

    public final SQLStmt sql2 = new SQLStmt(
        "SELECT * FROM XXXX WHERE YYYY ='French';");

    public VoltTable[] run(int kkk) throws VoltAbortException {

        if (kkk < 10) {

        voltQueueSQL( sql1 );
        return voltExecuteSQL();

        } else {

        voltQueueSQL( sql2 );
        return voltExecuteSQL();

        }

    }
}

See the Stored Procedures part of the docs for more information: https://docs.voltdb.com/tutorial/Part5.php

Full disclosure: I work at VoltDB.

Andrew
  • 311
  • 1
  • 8
  • Thanks Andrew.Is there any way to write the above code in c# ? I am not familiar with Java. – Cenoy Jun 20 '18 at 05:18
  • VoltDB only supports complex stored procedures written in Java. I recommend reading the docs I linked above starting with the "Writing More Powerful Stored Procedures" section. It outlines exactly how to do it in Java. – Andrew Jun 20 '18 at 15:00