sql - Db2 stored procedure to insert -

i'm new db2 , stored procedure created stored procedure insert records database stored procedure doesn't work, code follows:

drop procedure product_create ;                                                                                  create procedure product_create(                                                                                out errmsg       char(256),                                                                                     in  incucode       char(6),                                                                                       in  inprodcode     char(25),                                                                                      in  indescription  char(80),                                                                                      in  inupc          char(25),                                                                                      in  inuom          char(3),                                                                                       in  instdpack      decimal(5,0),                                                                                  in  inweight       decimal(9,4),                                                                                  in  inlength       decimal(9,4),                                                                                  in  inwidth        decimal(9,4),                                                                                  in  inheight       decimal(9,4)                                                                                   )  language sql  begin     if          errmsg = ' '      insert      prmast             ( prcucd,               prprcd,               prdes1,               prupc#,               prworu,               prwuts,               prntwt,               prlong,               prwide,               prhigh             )  values      ( incucode,               inprodcode,               indescription,               inupc,               inuom,               instdpack,               inweight,               inlength,               inwidth,               inheight,         ) ; end if ; return ; end 

is correct structure insert query in stored procedure forgetting anything, appreciated.

i reproduced issue following:

1. create table "prmast":

create table prmast(prcucd char(6), prprcd char(25), prdes1 char(80), prupc# char(25), prworu char(3), prwuts dec(5,0), prntwt dec(9,4), prlong dec(9,4), prwide dec(9,4), prhigh dec(9,4));

2. write stored procedure , save file called sol.txt

   create or replace procedure product_create(
out errmsg char(5),
in incucode char(6),
in inprodcode char(25),
in indescription char(80),
in inupc char(25),
in inuom char(3),
in instdpack decimal(5,0),
in inweight decimal(9,4),
in inlength decimal(9,4),
in inwidth decimal(9,4),
in inheight decimal(9,4)
) language sql begin
declare sqlstate char(5); insert prmast ( prcucd, prprcd, prdes1, prupc#, prworu, prwuts, prntwt, prlong, prwide, prhigh )values
( incucode, inprodcode, indescription, inupc, inuom, instdpack, inweight, inlength, inwidth, inheight ) ; set errmsg = sqlstate; end@

3. run procedure: db2 -td@ -vf sol.txt

4. then, call procedure:

   db2 "call product_create(?, 'a','b','c','d','e',6,7,8,9,10)"

   value of output parameters

   ---

   parameter name : errmsg parameter value : 00000

   return status = 0`

5. verify works:

`db2 select * prmast

prcucd prprcd prdes1 prupc# prworu prwuts prntwt prlong prwide prhigh

------ ------------------------- -------------------------------------------------------------------------------- ------------------------- ------ ------- ----------- ----------- ----------- ---------

a b c d e 6. 7.0000 8.0000 9.0000 10.00 00

1 record(s) selected.`

there several points noticed procedure:

• you can use create or replace procedure define procedure. drop existing procedure definition , replace definition run. in other words, don't have explicitly drop procedure

• char(255) out of range. declare output "errmsg" char(5) should enough.

• you can use set statement explicitly check if sql statement executed or not.

• the @ symbol @ end of procedure indicates db2 not use ; end of statement because use ; end of statement of sql, belongs part of stored procedure definition. why want use -td@ option when execute clp. tells db2 use @ instead of ; end-of-statement symbol.

• also, there , right after column "inheight", last column , should not have , following

• when calling stored procedure, ? required placeholder "out" parameter.

one more note, if want insert values selection result of table, may want use "cursor" instead of explicitly supplying values stored procedure.