having problem implementing stored procedure

shinichi90 · #1 (**permalink**) 09-01-10, 07:31

hi!! i'm trying to implement a stored procedure but somehow it wont work >< i think i followed the syntax correctly...

Code:

create procedure update_discount(in discount decimal(4,2))
language sql
begin
update Sales set Sales_Amount=Sales_Amount*discount where 
Sales_Quantity >=2
end

i create this in the command line of ibm db2...i read some other forums and websites saying we have to create a cursor? is it necessary?

thank you

regards,
shinichi90

tonkuma · #2 (**permalink**) 09-01-10, 08:22

Quote:

... but somehow it wont work

How did you know it wont work?

Did you got some messages?
If so, when(complie time? or execution time?) and what message(including code and full message text) did you received?

At least, please try to add semicolon, like this:
update Sales set Sales_Amount=Sales_Amount*discount where
Sales_Quantity >=2;

shinichi90 · #3 (**permalink**) 09-01-10, 08:41

it could not compile...i already tried putting semicolon after the query..
the error was..

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

thank you

regards,
shinichi90

wilsonfv · #4 (**permalink**) 09-01-10, 09:54

maybe you should build your stored procedure using command editor. input "db2ce" to open

shinichi90 · #5 (**permalink**) 09-01-10, 09:58

Quote:

Originally Posted by wilsonfv

maybe you should build your stored procedure using command editor. input "db2ce" to open

sorry i did it in command editor not command line...>< sorry my bad...

wilsonfv · #6 (**permalink**) 09-01-10, 11:01

maybe you should build your stored procedure using command editor. input "db2ce" to open

wilsonfv · #7 (**permalink**) 09-01-10, 11:04

if you are using command editor, add @ at the end of your procedure.

shinichi90 · #8 (**permalink**) 09-01-10, 11:34

Quote:

Originally Posted by wilsonfv

if you are using command editor, add @ at the end of your procedure.

Code:

create procedure procedure_name()
language sql
begin
//sql code
end@

like this? it came out the same error....>< what does this '@' stands for?

regards,
shinichi90

wilsonfv · #9 (**permalink**) 09-01-10, 12:44

Code:

create procedure procedure_name()
language sql
begin

sql code1;

sql code2;

end@

First, add @ at the end of your procedure.
Second, you should see an input field right at the bottom of the command editor named "Statement termination character".
Means if you set @ as "Statement termination character", the editor once see the @, it will know this is the end of your procedure.

Jack Vamvas · #10 (**permalink**) 09-01-10, 12:47

Try :
create procedure update_discount(in discount decimal(4,2))
language sql
begin
update Sales set Sales_Amount=Sales_Amount*discount where
Sales_Quantity >=2;
end@
TERMINATE@

then place in a file - and run from an interface - such as Putty - as :

db2 -td@ -f myfilename.sql

Internet Services

Web Development

Digital Marketing

Consumer Tech