Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread It would be nice if Stored Procedures/Functions could have default parameter values
Thu, Jun 27 2013 12:23 PMPermanent Link

Barry

I don't see why stored procedures and functions can't have a default value like they do in Delphi. (Like in Delphi, the parameters with a default value would have to appear after any parameters that are required.)

Example:

CREATE FUNCTION "fcn_GetStateName" (IN "aStateCode" VarChar Default "NY")

which can be called with a parameter or without a parameter:

select fcn_GetStateName('AL'), fcn_GetStateName();

I feel this would make the stored procedures and functions more flexible and easier to maintain because now the default values are stored in a central location, the database procedure instead of the client program which can have dozens of calls to the stored procedures.

Barry
Thu, Jun 27 2013 1:51 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Barry


If you search the sql newsgroup for a posting of mine with the subject "Passing a variable number of parameters to a function" it gives an idea. Without testing I don't know if it still does this and I don't remember Tim commenting either way wether it was fixed or not. If not then a simple check

IF aStateCode IS NULL THEN SET aStateCode = 'NY';

in the first lines of the function  would give you what you want. Not as elegant but it would work.

Roy Lambert [Team Elevate]
Fri, Jun 28 2013 2:25 AMPermanent Link

Barry

Roy,

Thanks for the link. It works (most of the time). I have to pass at least 1 parameter to the procedure. Example: If the procedure has 3 parameters defined, I can leave out parameters 2 and 3, or just 3. But if I leave them all out, I will get an error saying insufficient number of parameters.

So Tim, does this mean the bug has a bug? Smile

Barry
Tue, Jul 2 2013 12:50 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Barry,

<< I don't see why stored procedures and functions can't have a default
value like they do in Delphi. (Like in Delphi, the parameters with a default
value would have to appear after any parameters that are required.) >>

The reason for this is an SQL standard thing - the syntax was done
accordingly.  I'll see what I can do.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Jul 2 2013 12:57 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Barry,

<< Thanks for the link. It works (most of the time). I have to pass at least
1 parameter to the procedure. Example: If the procedure has 3 parameters
defined, I can leave out parameters 2 and 3, or just 3. But if I leave them
all out, I will get an error saying insufficient number of parameters.

So Tim, does this mean the bug has a bug? Smile>>

I'll have to check - it was probably changed in reference to an incident
report.  The parameters should be optional across the board, defaulting to
NULL if not specified.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Jul 2 2013 1:40 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


><< I don't see why stored procedures and functions can't have a default
>value like they do in Delphi. (Like in Delphi, the parameters with a default
>value would have to appear after any parameters that are required.) >>
>
>The reason for this is an SQL standard thing - the syntax was done
>accordingly. I'll see what I can do.

I don't know about Barry but I prefer the idea of setting defaults in the body of the function, as long as all / any parameters can be NULL

Roy Lambert
Sat, Jul 6 2013 1:04 AMPermanent Link

Barry

>I don't know about Barry but I prefer the idea of setting defaults in the body of the function, as long as all / any >parameters can be NULL

+1
That was my recommendation too. Smile

Barry
Image