Update rows with a variable value and use the default value if the variable is zero.

Question

Let's say I have a variable:

declare @x int = /* smth here */ 

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

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

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

UPDATE Therefore, I have the following ways:

• do this through system views - get the column_default property and use dynamic query execution

• perform both cases in separate subqueries:

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

  This method is simple and simple. Perhaps even the most transparent for the other guy who will read it further. Also keep in mind that this will also require separate inserts for

   @x is null /* use default */ 

  and

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

  cases

• create custom function:

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

  And then use it by default - (GetValueForColX(null)) and in the request to insert / update GetValueForColX(@x)

Answer 1

The default value specified in the system tables applies only to CLR procedures.

If you do not need two separate updates, use scalar UDF in both DEFAULT and the update. So instead of, say, GETDATE (), wrap it in CREATE FUNCTION, change the default value and update it to use this.

But two updates are easier ...

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