CODEWITHSUNDEEP
sqlsql-servert-sql
user1030181
user1030181

Reputation: 2015

Update query with ISNULL in SQL SERVER

DateTime Should update if the data is passed else update it with original value which is already saved. This update does not works

DECLARE @FolderStatusDate DATETIME = NULL

SET @FolderStatusDate = '2012-07-04 14:09:04.043'

UPDATE CM.PfmFolder     
  SET   
      FolderStatusDate = ISNULL(@FolderStatusDate, FolderStatusDate)
      WHERE Id = @Id

Upvotes: 1

Views: 6667

Answers (3)

Rajesh
Rajesh

Reputation: 1620

A slight edit on Hitesh Salian's answer

 DECLARE @FolderStatusDate DATETIME = NULL

 SET @FolderStatusDate = '2012-07-04 14:09:04.043'

 UPDATE CM.PfmFolder     
 SET   
 FolderStatusDate = case @FolderStatusDate is Null
                       then FolderStatusDate else @FolderStatusDate end
 WHERE Id = @Id

Upvotes: 0

Hitesh
Hitesh

Reputation: 3498

You can also do it this way

DECLARE @FolderStatusDate DATETIME = NULL

SET @FolderStatusDate = '2012-07-04 14:09:04.043'

UPDATE CM.PfmFolder     
SET   
  FolderStatusDate = case when ISNULL(@FolderStatusDate, '') = '' 
                           then FolderStatusDate else @FolderStatusDate end
  WHERE Id = @Id

Upvotes: 0

Raj
Raj

Reputation: 10843

Why don't you move the check for NULL to the WHERE clause?

DECLARE @FolderStatusDate DATETIME = NULL

SET @FolderStatusDate = '2012-07-04 14:09:04.043'

UPDATE CM.PfmFolder     
  SET   
      FolderStatusDate = @FolderStatusDate
      WHERE Id = @Id  
      AND @FolderStatusDate IS NOT NULL

Upvotes: 1

Related Questions