Stored Procedure Null Vlaues

[G.U.R.U Africa]

Hi!

I get an error when a send a null datetime and integer values to a stored procedure.


Error:
Application uses a value of the wrong type for the current operation.

cmdNP_AQUA.Parameters.Append cmdNP_AQUA.CreateParameter("@effective_to", adDate + adEmpty, adParamInput, 8, rsNP_ESERVER("effective_to")))

cmdNP_AQUA.Execute

Is there a way to send null values to a stored procedure?

[nswan]

it's best not to have null values in a database. Put blank times in for datetime such as "00:00:00" and may be 0's for integers.

[brucevde]

Is there a way to send null values to a stored procedure?

Absolutely. I do it all the time. The only possible problem that I see with the code you posted is the datatype paramter. Note the size parameter is only required for variable length columns.

Change adDate + adEmpty to just adDBTimeStamp

[techgnome]

The value for a "NULL" date (which a contradiction as you can't have a non date) is to use "12/31/1899". SQL Server will treat that value as similarto NULL.

[brucevde]

Any variable in SQL Server, which is what we are talking about here, can be set to Null. Even dates. In fact all variables are initialzed to Null when they are declared.

[jscontreras]

Not sure why you would want a date nullable. Have you thought about setting the date as a default value, i.e getdate(). Or just use the other strategies provided in this thread. Also if you want to add null or if a user doesn't enter a value this is how it's done

@DummyVar AS VARCHAR(10) = NULL /* This makes the parameter value optional */

Just throw an If condition such as

IF @DummyVar IS NOT NULL
BEGIN
IF RTRIM(@SQL) <> '' SET @SQL = @SQL + ',' + RTRIM (@DummyVar) + ' FROM TBLSample '
END

Hope this helps