NULL values and stored procedures

OK i am using SQL Server 2000 and working w/ stored procedures. I have looked everywhere i can to find the answer to my question w/o any luck...

All i need to do is insert null values in as a param in a stored procedure, but what i am seeing is that this is not working out well.

Stored Procedure:

CREATE PROCEDURE dbo.cmw_createFeedRecord
@.FeedType char(30) ,
@.UserID int ,
@.CostPerLB money ,
@.LBS int
AS
INSERT INTO
cattlemanagerweb.dbo.feedrecord ( feedtype , userid , costperlb , lbs )
VALUES
( @.FeedType , @.UserID , @.CostPerLB , @.LBS )
GO

Code:

System.Data.SqlClient.SqlParameter _FeedType = new System.Data.SqlClient.SqlParameter("@.FeedType" , System.Data.SqlDbType.Char , 30 );
_FeedType.Direction = System.Data.ParameterDirection.Input;
_FeedType.Value = "ok";
System.Data.SqlClient.SqlParameter _UserID = new System.Data.SqlClient.SqlParameter("@.UserID" , System.Data.SqlDbType.Int );
_UserID.Direction = System.Data.ParameterDirection.Input;
_UserID.Value = "1";
System.Data.SqlClient.SqlParameter _Cost = new System.Data.SqlClient.SqlParameter("@.CostPerLB" , System.Data.SqlDbType.Int );
_Cost.Direction = System.Data.ParameterDirection.Input;
_Cost.Value = "1.0";
System.Data.SqlClient.SqlParameter _LBS = new System.Data.SqlClient.SqlParameter("@.LBS" , System.Data.SqlDbType.Int );
_LBS.Direction = System.Data.ParameterDirection.Input;
_LBS.Value = "100";
System.Data.SqlClient.SqlCommand _Cmd = new System.Data.SqlClient.SqlCommand();
_Cmd.CommandType = System.Data.CommandType.StoredProcedure;
_Cmd.CommandText = "dbo.cmw_createFeedRecord";
_Cmd.Parameters.Add(_FeedType);
_Cmd.Parameters.Add(_UserID);
_Cmd.Parameters.Add(_Cost);
_Cmd.Parameters.Add(_LBS);

I want to set the _Cost.Value = "1.0"; to a null value to insert... Any help, or am i right and you can't insert null values?

Thanks anyoneone thing i would suggest is to set a default value of "0" for all columns tht r of type int or float or money or decimal..etc anything numeric.
then you wudnt have to worry abt nulls. it will be much convenient if you need to do calculations with those numeric columns...
for strings you can always use "null" and pass it as string..

HTH|||Hi,

You can assign a default NULL in your stored procedure as follows:

CREATE PROCEDURE dbo.cmw_createFeedRecord

@.FeedType char(30) = NULL,

@.UserID int = NULL,

@.CostPerLB money = NULL,

@.LBS int = NULL

AS

INSERT INTO

cattlemanagerweb.dbo.feedrecord ( feedtype , userid , costperlb , lbs )

VALUES

( @.FeedType , @.UserID , @.CostPerLB , @.LBS )

GO

If you do not add a parameter variable in your code the default NULL will be assigned and stored in your database. This also does work with the SQL UPDATE command.

Regards,
Wilco Swart|||:) thanks, it took me forever to figure that out, even thought it should be intitive :o|||Wilco Swart

Thank you!!!!

You just saved my sanity.

Bryan|||Just for the sake of completeness, and to be sure i'm doing it right myself...

In .NET, you can use DBNull.Value property for setting, and is operator or IsDBNull() function for testing.

E.g. _Cost.Value = DBNull.Value

-julio