Results 1 to 2 of 2
  1. #1
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221

    Unanswered: Db2 Stored Procedures

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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@
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •