How do I insert/update an empty date field with a stored procedure

I have a form with 3 optional date fields (smalldatetime).  Whenever one of the date fields is left empty and the stored procedure is executed I get the following error: "Error converting data type nvarchar to smalldatetime."  If a date is input into the date field II would like that date to be saved or if the date field is left empty then the stored procedure would enter a NULL value.
/****** Object:  StoredProcedure [dbo].[client_update]    Script Date: 01/04/2008 21:36:45 ******/
@clientid smallint,
@firstname varchar (50),
@lastname varchar (50),
@phone varchar (25),
@phone2 varchar (25),
@email varchar (50),
@type varchar (50),
@listdate smalldatetime,
@tailnumber varchar (10),
@listprice varchar (12),
@location varchar (50),
@website varchar (250),
@contacted varchar (10),
@lastcontactdate smalldatetime,
@initials varchar (2),
@status varchar (10),
@followupdate smalldatetime,
@archive varchar (3),
@comments varchar (1000)
UPDATE client
WHERE clientid=@clientid

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will have to pass NULL instead of ''

pigsooieAuthor Commented:
How do I do that?
Anthony PerkinsCommented:
>>How do I do that?<<
Unfortunately, while many of us are fairly competent, none of us are clairvoyant.  So unless we know/see how you are calling the stored procedure we cannot even remotely guess.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

pigsooieAuthor Commented:
I'm using ColdFusion
<cfstoredproc procedure="client_update" datasource="#dsn#">
<cfprocparam variable="@clientid" value="#id#" cfsqltype="cf_sql_smallint">
<cfprocparam variable="@firstname" value="#firstname#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@lastname" value="#lastname#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@phone" value="#phone#" cfsqltype="cf_sql_char">
<cfprocparam variable="@phone2" value="#phone2#" cfsqltype="cf_sql_char">
<cfprocparam variable="@email" value="#email#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@type" value="#type#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@listdate" value="#listdate#" cfsqltype="cf_sql_date">
<cfprocparam variable="@tailnumber" value="#tailnumber#" cfsqltype="cf_sql_char">
<cfprocparam variable="@listprice" value="#listprice#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@location" value="#location#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@website" value="#website#" cfsqltype="cf_sql_varchar">
<cfprocparam variable="@contacted" value="#contacted#" cfsqltype="cf_sql_tinyint">
<cfprocparam variable="@lastcontactdate" value="#lastcontactdate#" cfsqltype="cf_sql_date">
<cfprocparam variable="@initials" value="#initials#" cfsqltype="cf_sql_char">
<cfprocparam variable="@status" value="#status#" cfsqltype="cf_sql_tinyint">
<cfprocparam variable="@followupdate" value="#followupdate#" cfsqltype="cf_sql_date">
<cfprocparam variable="@archive" value="#archive#" cfsqltype="cf_sql_tinyint">
<cfprocparam variable="@comments" value="#comments#" cfsqltype="cf_sql_varchar">
<cfprocresult name="clientupdate">

Open in new window

Anthony PerkinsCommented:
I am afraid I have no idea how you pass a Null parameter using ColdFusion.  You may have better luck posting in that Zone.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try to add the following for the lines in question:
null="#(len(initials) is 0)#"

like this:

<cfprocparam variable="@initials" value="#initials#" cfsqltype="cf_sql_char" null="#(len(initials) is 0)#" >

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Null dates are a PIA, and quite a few applications don't even support null in date fields.  It's better to use some default dummy date, such as 01.01.1970 or 01.01.1900, instead of null values in date fields.
Anthony PerkinsCommented:
>>Null dates are a PIA, and quite a few applications don't even support null in date fields.<<
I beg to differ with you on all counts.  First it is not the application that supports or does not support Nulls, but the back end database.  In this specific case we are talking about MS SQL Server which doess support it as most good RDMS do.  Why in the world would you resort to using a "dummy" value when there is a perfectly valid values to use?

We should be encouraging the questioner to use good relational concepts, rather than finding artificial workarounds.
pigsooieAuthor Commented:
Thank you angellll, that's exactly what I was looking for.
Avoiding null values in general in the database is a good practice IMO.  Sure, there are valid uses for null, but not many, and they increase the complexity of your queries unnecessarily.  You can't use regular comparison operator on nulls, but have to use the IS operator.  Not all host languages support nulls, so you may have to make sure that you don't return null in your queries.  I could go on, but better to quote Joe Celko's 'SQL for Smarties, 2nd ed.': :"Think of NULLs as a drug; use it properly and it works for you, but abuse it and it can ruin everything.  Your best policy is to avoid them when you can and use them properly when you have to"
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.