asked on

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)
 
AS
 
UPDATE client
SET
 
firstname=@firstname,
lastname=@lastname,
phone=@phone,
phone2=@phone2,
email=@email,
type=@type,
listdate=@listdate,
tailnumber=@tailnumber,
listprice=@listprice,
location=@location,
website=@website,
contacted=@contacted,
lastcontactdate=@lastcontactdate,
initials=@initials,
status=@status,
followupdate=@followupdate,
archive=@archive,
comments=@comments
 
WHERE clientid=@clientid
 
GO

Open in new window

Guy Hengel [angelIII / a3]

you will have to pass NULL instead of ''

pigsooie

ASKER

How do I do that?

Anthony Perkins

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

pigsooie

ASKER

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">
</cfstoredproc>

Open in new window

Anthony Perkins

I am afraid I have no idea how you pass a Null parameter using ColdFusion. You may have better luck posting in that Zone.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Maxi84

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 Perkins

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

pigsooie

ASKER

Thank you angellll, that's exactly what I was looking for.

Maxi84

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"