Update rows with variable's value and use default if variable is null

Question

Let's say I have a variable:

declare @x int = /* smth here */

And I have a table with column (e.g. ColX) which is not null and have a default constraint. Now I want to update some rows in the table in a way like this:

update MyTable
set ColX = isnull(@x, default)
where blah-blah-blah

Obviously it can't be executed since sql-server will show you an error, but I think this example clearly reflect what I want to do. So the question is how it can be done?
Thanks!

UPDATE So I have now these ways:

• do it through system views - get column_default propery and use dynamic query execution

• perform both cases in separate subqueries:

  if @x is null then update /* use default */ else update /* use @x */
  

  This way is a straightforward and the simpliest. Maybe even the most transparent for the other guy who'll read it further. Also keep in mind it will require also to have separate inserts for both

  @x is null /* use default */ 
  

  and

  @x is not null /* use @x */
  

  cases

• create user-defined function:

  create function GetValueForColX(@value int) returns int
  as begin
      return isnull(@value, /* default value */)
  end
  

  And then use it in default - (GetValueForColX(null)) and in insert/update query GetValueForColX(@x)

Answer 1

The default shown in system tables applies to CLR procs only.

If you don't want 2 separate updates, use a scalar UDF in both the DEFAULT and in the update. So instead of, say, GETDATE(), wrap this in CREATE FUNCTION, change the default and update to use this.

But two updates is simpler...

Answer 2

Possible workaround using two updates

DECLARE @x int
IF @x IS NULL
  update MyTable
  set ColX = DEFAULT
ELSE 
  update MyTable
  set ColX = @x