CODEWITHSUNDEEP
sqlsql-servert-sql
user3452210
user3452210

Reputation: 147

Using COALESCE in Sql server

I have a stored procedure which will update the album details if a album id is present in the table , if not it will add a new record. User some times can update all values, or some times leaves some values. So in case if the user leave the values for updating old data should be kept in table. For this i found solution to make use of coasesce in sql...

I wrote my SP as:

 UPDATE [dbo].[tbl_M_Album]

   SET [AlbumName] = @AlbumName
      ,[ImageName] = @Imagename
      ,[Description] = coalesce(@Description,[Description])
      ,[Imagepath] = @Imagepath
      ,[UpdatedBy] = @CreatedBy
      ,[UpdatedDate] = @CreatedDate
      where AlbumID =@AlbumId
      end

If i did not send the Description the old data is not keeping, it is overriding by empty data. Please some one help me if i have any mistakes..

Upvotes: 0

Views: 145

Answers (3)

Jodrell
Jodrell

Reputation: 35746

The value of @Description you pass is blank or empty, not NULL.

COALESCE and other null checking functions treat '' as a non null value.

These are all true,

NULL IS NULL

'' IS NOT NULL

' ' IS NOT NULL

If you want to check for NULL, '' (empty) or ' ' (whitespace), you could use

COALESCE(LEN(TRIM(@Description)), 0) = 0

or, for just NULL and empty,

COALESCE(LEN(@Description), 0) = 0

but, it would be more efficient to avoid passing empty or blank values.

You could rewrite your SP like this

IF COALESCE(LEN(TRIM(@Description)), 0) = 0
    UPDATE [dbo].[tbl_M_Album]
        SET
                 [AlbumName] = @AlbumName
                ,[ImageName] = @Imagename
                ,[Imagepath] = @Imagepath
                ,[UpdatedBy] = @CreatedBy
                ,[UpdatedDate] = @CreatedDate
        WHERE
                 [AlbumID] = @AlbumId;
 ELSE
     UPDATE [dbo].[tbl_M_Album]
        SET
                 [AlbumName] = @AlbumName
                ,[ImageName] = @Imagename
                ,[Description] = @Description
                ,[Imagepath] = @Imagepath
                ,[UpdatedBy] = @CreatedBy
                ,[UpdatedDate] = @CreatedDate
         WHERE
                 [AlbumID] = @AlbumID;

Upvotes: 3

Eduard Uta
Eduard Uta

Reputation: 2617

Check if the value assigned for @Description is an empty string or NULL. If you pass an empty string in @Description then that is the problem. Only passing NULL will "default" to the next non-null value (this applies to both COALESCE or ISNULL).

Hope this helps.

Upvotes: 1

Saravana Kumar
Saravana Kumar

Reputation: 3729

Use this query. 

UPDATE [dbo].[tbl_M_Album]

   SET [AlbumName] = @AlbumName
      ,[ImageName] = @Imagename
      ,[Description] = Case WHEN (@Description IS NULL OR LTRIM(RTRIM(@Description) = '') 
                            THEN [Description]
                            ELSE @Description
      ,[Imagepath] = @Imagepath
      ,[UpdatedBy] = @CreatedBy
      ,[UpdatedDate] = @CreatedDate
      where AlbumID =@AlbumId
      end

Upvotes: 0

Related Questions