CODEWITHSUNDEEP
firebirdflamerobin
Andiastika Intan Pratiwi Hasan
Andiastika Intan Pratiwi Hasan

Reputation: 87

Getting an error of 'Token Unknown' while Execute Stored Procedures

I'm new in learning stored procedures in SQL.

I want to create a stored procedure for inserting values from automatic data by calculation.

Table Attendance:

EMPL_KODE |EMPL_NAME  |DATE_IN    |TIME_IN |TIME_OUT|TIME_IN |TIME_OUT
001       | Michel    |25.04.2016 |06:50   |15:40   |        |
002       | Clara     |25.04.2016 |06:15   |15:43   |        |
003       | Rafael    |25.04.2016 |06:25   |15:45   |        |
001       | Michel    |26.04.2016 |06:23   |15:42   |        |
002       | Clara     |26.04.2016 |06:10   |15:41   |        |
003       | Rafael    |26.04.2016 |06:30   |15:42   |        |
001       | Michel    |27.04.2016 |06:33   |15:42   |        |
002       | Clara     |27.04.2016 |06:54   |15:44   |        |
003       | Rafael    |27.04.2016 |07:00   |15:45   |        |

I want to fill TIME_IN and TIME_OUT values automatically by creating a stored procedure. Here is the code :

CREATE PROCEDURE InsertTotalEmployee
    @TOTAL_MINUTES int,
    @TOTAL_HOURS float
AS
BEGIN
    INSERT INTO ATTENDANCE (TOTAL_MINUTES, TOTAL_HOURS)
    VALUES (
       SELECT 
           DATEDIFF(MINUTE, ATTENDANCE.TIME_IN, ATTENDANCE.TIME_OUT),
           DATEDIFF(MINUTE, ATTENDANCE.TIME_IN, ATTENDANCE.TIME_OUT) / 60.0
    )
END

After I write and execute my statement, a message error occurs:

Token unknown - line 2, column 5 @

I run the code using Flamerobin.

Upvotes: 0

Views: 1321

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109239

It looks like you are trying to use Microsoft SQL Server syntax in Firebird, that is not going to work.

For one, the @ is not allowed like that in identifiers (unless you use double quotes around them), and the list of parameters must be enclosed in parentheses.

See the syntax of CREATE PROCEDURE. You need to change it to:

CREATE PROCEDURE InsertTotalEmployee(TOTAL_MINUTES int, TOTAL_HOURS float)

You also might want to change the datatype float to double precision, and the body of your stored procedure seems to be incomplete because you are selecting from nothing (a select requires a table to select from), and are missing a semicolon at the end of the statement.

All in all I suggest you study the Firebird language reference, then try to create a functioning insert and only then create a stored procedure around it.

Also note that when creating a stored procedure in Flamerobin, that you must switch statement terminators using set term otherwise Flamerobin can't send the stored procedure correctly, see also the first section in Procedural SQL (PSQL) Statements.

Upvotes: 1

Related Questions