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 :\

As i found from this [link](http://stackoverflow.com/questions/982798/is-it-possible-to-have-a-default-parameter-for-a-mysql-stored-procedure) **It's still not possible.** You can add your default parameter in Where clause something like this WHERE id = @id or id = "some default value" — The Reason, Jun 02 '15 at 07:49
This is the mysql 5.1 create stored procedure documentation: https://dev.mysql.com/doc/refman/5.1/en/create-procedure.html It says nothing about default declaration :/ — bish, Jun 02 '15 at 07:51
@bish yeah, I've been through that and many docs, I can't find anything usefull for my prob. it sucks. older versions could do it. N.Molderf thanks , that assures me I can't get it done in 5.1. so should I use an older version using TYPE1(in my ques) syntax? which version would be the best for this? — runTimeError, Jun 02 '15 at 08:08

score 2 · Answer 1 · answered May 15 '16 at 14:03

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!

sql stored proc "Default Parameter Values"

1 Answers1