asked on

Stored Procedure and Null Values

I have a SQL stored procedure that is called by a c# webservice. There are dates in the process, sometimes the dates will be null so I want to write nothing. Problem is I get a SQL Exception from the stored procedure if I do not put a value in the parameter. How can I get around this.

Code included

--- Sored procdure to update record 
create  PROCEDURE [dbo].[updateClientInformation] (
	@ClientApplicationID INT,
                @RecordType Varchar(1),
	@FirstName varchar(50),
	@LastName varchar(50),
	@ApplicationDate DATETIME,	
	@ApprovalDate DATETIME
) AS
UPDATE 
	dbo.ClientInformation
SET
	FirstName = @FirstName,
	LastName = @LastName,
	ApplicationDate = @ApplicationDate,	
	ApprovalDate = @ApprovalDate
WHERE
	 ClientApplicationID = @ClientApplicationID AND
	 RecordType = @RecordType
	 
	 
	 
C# code 	 
	 
SqlParameter ApprovalDate = new SqlParameter("@ApprovalDate", SqlDbType.DateTime);
ApprovalDate.Value = p.ApprovalDate;
command.Parameters.Add(ApprovalDate);
 
// would like to do
//
if(p.ApplicationDate != null)
{
  SqlParameter ApprovalDate = new SqlParameter("@ApprovalDate", SqlDbType.DateTime);
  ApprovalDate.Value = p.ApprovalDate;
  command.Parameters.Add(ApprovalDate);
}
//
 
 
But this causes an error in the stored procedure because ApprovalDate parameter

Open in new window

Aneesh

create PROCEDURE [dbo].[updateClientInformation] (
@ClientApplicationID INT,
@RecordType Varchar(1),
@FirstName varchar(50),
@LastName varchar(50),
@ApplicationDate DATETIME,
@ApprovalDate DATETIME = NULL

Aneesh

and which value you want to put for that column in case it is null, if you wanna keep the existing value, change the update like this

UPDATE
dbo.ClientInformation
SET
FirstName = @FirstName,
LastName = @LastName,
ApplicationDate = @ApplicationDate,
ApprovalDate = ISNULL(@ApprovalDate, ApprovalDate)
WHERE

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

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

Start Free Trial

BrandonGalderisi

As aneesh has shown by example, you can supply a default value.

Here's my experiences...

The .Net data provider, when passing NULL to a parameter, chooses to not pass the parameter at all. I don't know if that is an option, but I personally don't care for it.

Instead, it ASSUMES that if you are passing NULL that it must be acceptable to use the default value for a parameter. What if the default was getdate() and you TRULY wanted NULL. I guess .Net's stance on that is that you should have NULL as the default and pass getdate() when you want it.

Charles Baldo

ASKER

Thank You much it did the trick