• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5637
  • Last Modified:

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

0
askolits
Asked:
askolits
1 Solution
 
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
 
askolitsAuthor Commented:
Oh well, I left a comment on the MySQL link you sent. Thanks for your help.

John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now