Stored Procedure Parameter Code

[scottnweber]

I am new to CF and I am trying to understand some CFSTOREDPROC code. My question is withing this code there is sometimes a CFIF statement to determine how the CFPROCPARAM is set, whereas with other lines this is not done.

<CFPROCPARAM VALUE="#arguments.frmstruct.cutomerIdAutoValueContainer#" cfsqltype="CF_SQL_VARCHAR"

vs

<cfif

isdefined('arguments.frmstruct.SALESPERSON') and len(arguments.frmstruct.SALESPERSON) gt 1>

<cfelse>

     <CFPROCPARAM VALUE="" cfsqltype="CF_SQL_VARCHAR">

</cfif>

Does this make sense to have the code like this and why? Couldn't we just have the CFPROCPARAM without the CFIF to test the value if GT 1?

i.e.     <CFPROCPARAM VALUE="#arguments.frmstruct.SALESPERSON#" cfsqltype="CF_SQL_VARCHAR">

     <CFPROCPARAM VALUE="#arguments.frmstruct.SALESPERSON#" cfsqltype="CF_SQL_VARCHAR">

[Carl Von Stetten]

My guess would be that it is providing some validation and error trapping.  If arguments.frmstruct.SALESPERSON didn't exist and you didn't check first, then using arguments.frmstruct.SALESPERSON in the CFPROCPARAM tag would throw an error.  So this is what the isDefined() part is doing for you.  The other part (the len() function) makes sure that the value of arguments.frmstruct.SALESPERSON is more than one character long.  This seems a little odd to me, unless that is a valid business rule that must be enforced.

If the intent is really to trap for empty strings or strings with just spaces, that can be done more effectively by changing this:

len(arguments.frmstruct.SALESPERSON) gt 1

to this:

len( trim( arguments.frmstruct.SALESPERSON ) )

this is using the side-effect that any non-zero integer is treated as TRUE by ColdFusion; so if after trimming spaces off of the value it still has a length greater than zero, this will still return TRUE to <CFIF>.

Now another thing you need to know is if that database column you are passing the data allows NULL values.  If it doesn't, then the above code will work fine.  If however, NULLS are allowed (and desired) for this database column, then you'll want to change this:

<cfelse>

        <CFPROCPARAM VALUE="" cfsqltype="CF_SQL_VARCHAR">

</cfif>

to this:

<cfelse>

        <CFPROCPARAM VALUE="" NULL="yes" cfsqltype="CF_SQL_VARCHAR">

</cfif>

which will pass an actual NULL value to that database column.

HTH,

-Carl V.

[scottnweber]

Thanks Carl, that is very helpful to me.

[BKBK]

This is in addition to what Carl has said. In answer to your question, yes, you could just have cfprocparam, without the cfif. The cfif is optional.

The variable arguments.formStruct implies we are in the context of a function. It also implies that a structure is being passed as an argument. You could give this argument the attribute required="yes". This ensures the function will only proceed if the argument is passed.

Going by the naming you use, the structure is a form or a copy of a form, and salesPerson is one of its fields. The scenario suggests that the form will always exist, as will the salesPerson field. If that is indeed so, then there is no need to test for existence. However, one obvious consequence is that you will be storing an empty string in the database when the salesPerson field is blank.