Is there any way to define a default parameter in a sql stored procedure?

No, not in DB2 for LUW.

Andy

Thanks
It is actually z/OS (AS/400) used over iSeries but I assume as long as it is SQL it will be the same. Couldn't find anything in the manual about default parameters.

z/OS and AS/400 (iSeries) are two completely different platforms and the DB2 products for both platforms are also different.

You could write the SP to determine if a parameter needs a value (it is null) and then supply the default.

IF Arg1 is null then set arg1 = <default>; end if;

You would do this as the first executable statements of the SP.

Andy

ok, sorry for being thick.
It is as400 - iseries.

Thanks mate, that is pretty much what I have done. However, you still need to pass the argument in the call. I guess I can live with this shortcoming.

If you want to do it where the argument is left out altogether, then you basically have a different SP--it has a different signature. You can create one like that, it just has to have a different SPECIFIC NAME. It can have the same called name. You could even have the SP with the missing parameter call the other one and supply the value for the missing parameter with the default.

Andy

That's a good idea. Thanks for the tip.