In SQL Server, when using optional parameters, the only way to have default values is using @param = NULL upon creating.

Is there a way for the stored procedure to know that the application will only update specific columns and the remaining columns will retain the original values?

Here is my example:

Data: col1 = "AA" col2 = "BB" col3 = "CC"

Application update inside stored procedure: UPDATE table SET col3 = "C", col ="B" WHERE id = 1

After update, value of col1 will remain.

I hope my explanation is clear enough :)

  • Additional question, Is it also advisable to combine inserting and updating records in a same stored procedure (Logic: if insert, insert records, if update, update record)? – Edgar Allan Bayron Nov 21 '16 at 10:01
  • You can use IF ELSE for check all parameters and prepare statements, than execute it. and Yes - You can have in same procedure as much number of operations as You need. Again by parameters You can manage - do all of them of just selected – a_vlad Nov 21 '16 at 10:25
  • If you are only concerned with not updating those columns that have NULL parameters you could also go with col3 = COALESCSE(@param, col3)... – Nic Nov 21 '16 at 14:35

If I understood correctly, if stored procedure is called with @vCol1 = 'AA', @vCol2 = 'BB' and @vCol3 = NULL then you want to update only Col1 and Col2 keeping the Col3 value intact. Then you can go with the following solution. I agree its not the best one but it will get work done in simple manner.

UPDATE table
SET col1 = ISNULL(@vCol1, col1)
  , col2 = ISNULL(@vCol2, col2)
  , col3 = ISNULL(@vCol3, col3)
WHERE id = 1
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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