I think I am somehow close to get it work, but for some reason I still get errors.

I have the following UPDATE query:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories' THEN @Accessories
          WHEN 'Description' THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
WHERE DeviceID = 10

The parameters are filled in by different users. The problem I face now is that even when you want to update a single field you still have to fill out with the old data the other parameters. Therefore I would like to set the option IF @parameter IS NULL THEN keep the value that is already stored in the DB. I tried to find some solution and something like the following query seems to be the solution but I can't get it to work:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories' IS NOT NULL THEN @Accessories
          WHEN 'Description' IS NOT NULL THEN @Description
          WHEN 'Specification' IS NOT NULL THEN @Specification
          ELSE Details
WHERE DeviceID = 10

THE DB is stored in SQL Server 2008

Thanks in advance for the help.

EDIT for clarifying:

The original table looks like this

DeviceID|Name         |Details   |
     10 |Accessories  |earplugs  |
     10 |Description  |protectors|
     10 |Specification|BeatsByDre|

For publishing reasons I made a query to turn the tables. So when the Select query is ran the return table looks like the following

     10 |earplugs   |protectors |BeatsByDre   |

I created a UI to let system users update the different fields like Accessories, Description,Specification. The Update works if I update all fields with the query shown in the top. However when I Leave a textbox empty, then I get an error that the @parameter is missing a value. So trying to find a solution to update only the field where is something written. So if @parameter IS NULL then keep the original value in the DB. I found another solution which is really close to what I want but can't get it to work. Here is the other solution: https://stackoverflow.com/questions/9387839/mysql-if-not-null-then-display-1-else-display-0

  • Can you provide this exact error? Is it an SQL-Server error or an error in your (UI) application? What UPDATE statement does the UI send to the database? Mar 15, 2013 at 11:06
  • Can you add that in the question? It may be with how the statement is constructed and what quotes are used. The '@'Accessories doesn't make sense to me. Mar 15, 2013 at 11:51
  • 1
    I think that your UI code may be not producing valid statements. Show us the code you have and produces the error, not pseudo-code. Mar 15, 2013 at 12:30
  • I want to thank you agaim for the help. Sorry for my lack of experience. I managed to find the error, and you were right the problem was at the UI level. Thank you so much for the help. In case someone is looking for a similar answer I used the first solution
    – Greenhorn
    Mar 15, 2013 at 14:32

1 Answer 1


I think this will solve the issue:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN COALESCE(@Accessories, Details)
          WHEN 'Description'   THEN COALESCE(@Description, Details)
          WHEN 'Specification' THEN COALESCE(@Specification, Details)
          ELSE Details
WHERE DeviceID = 10 ;

or this (to avoid redundant updates):

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories'   THEN @Accessories
          WHEN 'Description'   THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
WHERE DeviceID = 10 
  AND ( Name = 'Accessories'   AND @Accessories IS NOT NULL
     OR Name = 'Description'   AND @Description IS NOT NULL
     OR Name = 'Specification' AND @Specification IS NOT NULL
      ) ;

or this, using a table value constructor:

SET da.Details = upd.Details
FROM DeviceAttribute AS da
    ( VALUES
      ('Accessories'   , @Accessories),
      ('Description'   , @Description),
      ('Specification' , @Specification)
    ) AS upd (Name, Details)
    ON upd.Name = da.Name 
WHERE da.DeviceID = 10
  AND upd.Details IS NOT NULL ;
  • Thanks for the quick answer. I will try it out and let you know of the result.
    – Greenhorn
    Mar 15, 2013 at 9:59
  • You mean the 2nd solution in my answer? Mar 15, 2013 at 10:09
  • yes, I am trying the 3rd solution, I upvoted the answers because it gives lots of possibilities. I ty the 3rd and if I get it to work I will accept it also.
    – Greenhorn
    Mar 15, 2013 at 10:10
  • All should work. If they don't, you can edit your question and add the table structure (Create Table statement) and the Stored Procedure. There may be something relevant that I misunderstand or you didn't mention. Mar 15, 2013 at 10:35
  • Id these answers work when all parameters are not null but do not work when one of them is null, then look at the UI code and how it handles null when producing the query. And you could add that (UI) code at the question. Mar 15, 2013 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.