Db2 Stored Procedures

nick.ncs · #1 (**permalink**) 06-26-07, 03:47

can u specify default values to parameters of stored procedures if they are not supplied??

for eg

create procedure owner.A
(
in X int ,
in Y char(4) ,
in Z varchar(3)
)

it may be possible that for some cases value of Z will not be supplied and i want it to take default values

something in the lines of

create procedure owner.A
(
in X int ,
in Y char(4) ,
in Z varchar(3) default null
)

so if i call procedure A as A(1,2) will it run as A(1,2,null) since by default Z is set to null

stolze · #2 (**permalink**) 06-29-07, 03:07

What do you mean with "not supplied"? When you call a stored procedure, DB2 will identify the proper procedure by the fully qualified name and the number of parameters. Thus, if you omit a parameter, it will resolve to a different procedure.

The only way to achieve what you want is to write two procedures:

Code:

CREATE PROCEDURE proc(IN x INT, IN y CHAR(4), IN z VARCHAR(3))
   ...

CREATE PROCEDURE proc(IN x INT, IN y CHAR(4))
   LANGUAGE SQL
   BEGIN
      CALL proc(x, y, CAST(NULL AS VARCHAR(3));
   END@

Internet Services

Web Development

Digital Marketing

Consumer Tech