sql stored proc "Default Parameter Values"

Question

I'm trying to set "Default Parameter Values" in stored proc. i know the syntax TYPE 1:

CREATE PROCEDURE proc_name @param nvarchar(30) = NULL, @param1 nvarchar(60) = NULL

My TOAD 7.3 using mysql 5.1.35 is giving a syntax of TYPE 2: when I create new Procedure

`CREATE PROCEDURE name (param_name param_type)
for e.g: create procedure test (IN name varchar(50))

but i can't find the right syntax to set 'IN' values

create procedure test (in name varchar(50) = null) 

is throwing syntax error.

please tell me the right syntax to set default parameters in type 2. I've searched a lot in and out of SO for this, no luck :\

Answer 1

You can't explicitly do that, but you could simulate it, in some cases. I just now had a case of that, where the parameter I wanted a default value to, was being checked in an IF statement. Here's how I did it:

In the body of my condAddNewLineTo, I had if statement. Since my function was to conditionally return things, I simply put my default behavior outside of those if-statements. Like this:

IF varToCheck = <nonDefaultValue> THEN
    #some lines of code to execute
    RETURN <value>;
END IF;
# default behavior here
RETURN <defaultValue>;

Alternately, if not writing stored function that returns stuff, you could do something like:

IF varToCheck = <nonDefaultValue> THEN
    # some lines of code to execute
ELSE 
    # some default code
END IF;

Hope this helps!