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

Charles BaldoSoftware DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
create  PROCEDURE [dbo].[updateClientInformation] (
      @ClientApplicationID INT,
                @RecordType Varchar(1),
      @FirstName varchar(50),
      @LastName varchar(50),
      @ApplicationDate DATETIME,      
      @ApprovalDate DATETIME = NULL
0
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
Aneesh RetnakaranDatabase AdministratorCommented:
here is the complete sp
--- Sored procdure to update record 
create  PROCEDURE [dbo].[updateClientInformation] (
      @ClientApplicationID INT,
                @RecordType Varchar(1),
      @FirstName varchar(50),
      @LastName varchar(50),
      @ApplicationDate DATETIME = null ,      
      @ApprovalDate DATETIME = null 
) AS
UPDATE 
      dbo.ClientInformation
SET
      FirstName = @FirstName,
      LastName = @LastName,
      ApplicationDate = ISNULL(@ApplicationDate,ApplicationDate) ,    
      ApprovalDate = ISNULL(@ApprovalDate,ApprovalDate) 
WHERE
       ClientApplicationID = @ClientApplicationID AND
       RecordType = @RecordType
       

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrandonGalderisiCommented:
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.
0
Charles BaldoSoftware DeveloperAuthor Commented:
Thank You much it did the trick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.