How to use optional parameters in a MySQL Stored Procedure

I want to modify a SP in MySQL, but don't want it to crash apps currently using that SP.

The SP is: SP_CreateUserReport
Argument 1: Check4ItCode
Argument 2: PasscodeAdmin

Currently, the calling app sends something like:
Call SP_CreateUserReport('CfiCode','12324')

But I want to add a new argument to the SP:

Argument 3: DemoUserEmail

When I do that, and do the same call, the SP will not receive the 3rd new argument and fail.

Eventually, I will update the all the calls from various apps to add the 3rd argument value, But in the meantime, I want to make that 3rd argument optional till I can update all the calling apps. And, if that value is not there, store a NULL in the table for that field.

Here is a sample of the final code I wish to use.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_CreateUserReport`(
Check4ItCode varchar(255) ,
PasscodeAdmin varchar(255),
DemoUserEmail varchar(255) 
)

BEGIN
INSERT INTO tbl_000_010_MAIN_REPORT_INFO ( 
strCFICode, 
strPasscodeAdmin, 
strDemoUserEmail)

SELECT 
Check4ItCode as CFICode, 
PasscodeAdmin AS AdminCode, 
DemoUserEmail as TheDemoUserEmail

SELECT LAST_INSERT_ID() as NewReportID;
END

Open in new window

askolitsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
According to the MYSQL team, this is a request since 2005, but so far, it was not implemented:
http://bugs.mysql.com/bug.php?id=15975

This means that you cannot accomplish what you want, so it is better to think in a different approach.

HTH,
Walter.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
askolitsAuthor Commented:
Oh well, I left a comment on the MySQL link you sent. Thanks for your help.

John
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.