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

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..

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>

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>