Richard,

Try this:
        MyStoredProc.ParamByname('MyDate').Value := null;
        MyStoredProc.ExecProc;

Phil Cain
--

You need to clear the parameter and then set a flag.. like so..

Param.Clear;
Param.Bound := True;

Read up on the TParam property Bound for explanation...

news:87vce7$cr719@bornews.borland.com...

news:ma47aso74sc807k36laule161ebdqt2v00@4ax.com...

Phil,

This should categorically not work.  I've never gotten it to work for SQL
databases.  See my other reply.

D4, SQL Server 7

I have a stored procedure which inserts a number of values into a table. It
receives the values from a TStoredProc. Some of the values are dates
(shortdatetime), which have a table default of NULL.

When there is no value sent to the stored procedure (ie the parameter of the
TStoredProc is set to ''), the stored procedure inserts '01/01/1900'.

How do I get the stored procedure to insert a NULL value for the date?

Thanks for your help,
Richard

===================================
CREATE PROCEDURE MyStoredProcedure
 @MyDate1 smalldatetime,
 @mydate2 smalldatetime,
 @MyDate3 smalldatetime,
 @etc

 INSERT INTO MyTable(
    MyDate1,
    MyDate2,
    MyDate3,
    etc)
  VALUES(
   @MyDate1,
    @MyDate2,
    @Mydate3,
    @etc)

How did you checked for "01/01/1900" value?

In BDE, when a date is null the AsDateTime method returns '01/01/1900' which
is the base for smalldatetime type. You need to check the property
Query.FieldByName('field').IsNull to see if the value is null, because the
type TDateTime is a double and can not handle null values.

Arthur

Richard Proudfoot escreveu na mensagem <87vce7$cr...@bornews.borland.com>...

Hi Philip!

On Thu, 10 Feb 2000 22:35:26 -0600, Philip Cain <philc...@orelle.com>
wrote:

Better

MyStoredProc.ParamByname('MyDate').Clear;

tomi

You're right. Forgot about the clear method.

Phil
--

Arthur,

Firstly, I am using ADO, but the end result is the same in the database. To
answer your question, I am simply observing the result in the SQL Server
table.

Your point raises an interesting question.

To get round the problem, I simply avoided inputting a value to any date
fields where the date is not filled in on the form, but using 'Clear' is
obviously the proper way to handle this situation and that's what I will do.
Thanks guys for telling me what I should have been able to track down
myself!!

But in view of your point about TDateTime being double, I am interested to
understand what happens when you use Param.Clear.  What does it set the
TDateTime field to if not NULL?

Almost certainly a naive question, I know, but that's me!

Regards,
Richard

Perry,

Thanks for that. It was new to me.

Having read the help file, I am still unsure whether I should be setting
Bound to True or false. The help file says that 'when Bound is False, a
TStoredProc object attempts to supply a value directly from the server' -
does that mean that it leaves the server to supply  my default table value
of NULL?

This seems to conflict a bit with what went before...
'The Clear method will replace the value of the parameter with NULL, but
will not set Bound to False. If the Clear method is used to bind the
parameter to a NULL value, Bound must be separately set to True.'

I guess it depends on whether you want the TStoredProc or the Server default
to supply the NULL value. In view of Arthur Heinrich's comment  (see thread)
about not using NULL with a TDateTime field I am uncerain which is the
correct, best or only way to go?

Regards,
Richard