How to handle default parameter in SQL Stored Procedure call in ColdFusion?

Question

Hi,

I've created an SQL stored procedure:

CREATE PROCEDURE usp_MyTableInsert
...
@name varchar(100),
@birthdate datetime = NULL,
@phoneno varchar(10),
...

And I call it from ColdFusion code:

<cfstoredproc 
   datasource="training"
   procedure="usp_MyTableInsert">
   ....
   <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#form.name#" dbvarname="@name">
   <cfprocparam cfsqltype="CF_SQL_DATE" value="#ParseDateTime(form.birthdate)#" dbvarname="@birthdate">
   <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#form.phoneno#" dbvarname="@phoneno">
   ....
</cfstoredproc>

What if I don't want to pass birthdate as a parameter? It should take NULL value, as I set it as default value in SP. If I remove it, It gives me an error like cannot convert varchar to datetime, that means we need to pass all parameter in correct order.

Thanks..

Answer 1

See the docs.

<cfstoredproc datasource="training" procedure="usp_MyTableInsert">
    ....
    <cfprocparam value="#form.name#" dbvarname="@name">
    <cfif IWantToPassInBirthday eq "YES">
        <cfprocparam value="#ParseDateTime(form.birthdate)#" dbvarname="@birthdate">
    <cfelse>
        <cfprocparam null="YES" dbvarname="@birthdate">
    </cfif>
    <cfprocparam value="#form.phoneno#" dbvarname="@phoneno">
    ....
</cfstoredproc>

Answer 2

My personal preference is to use a shortcut for the "null" attribute, using whatever date check is appropriate. But both techniques are valid as long as you supply all of the necessary attributes, such as cfsqltype.

On a side note "dbvarname" is deprecated, so do not use it in new applications.

<cfstoredproc 
   datasource="training"
   procedure="usp_MyTableInsert">
   ...
   <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#form.name#">
   <cfprocparam cfsqltype="CF_SQL_DATE" value="#form.birthdate#" null="#not IsDate(form.birthdate)#">
   <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#form.phoneno#">
   ....
</cfstoredproc>