假设我有一个变量:
declare @x int = /* smth here */
我有一张表,其中一列(例如ColX
)是not null
并具有default
约束。现在我想以以下方式更新表中的某些行:
update MyTable
set ColX = isnull(@x, default)
where blah-blah-blah
显然,由于 SQL Server 会显示错误,因此无法执行此操作。但我认为这个例子清楚地反映了我的意图。 那么问题是如何实现呢?
谢谢! 更新 现在我有以下几种方式:
- 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 queryGetValueForColX(@x)
((0))
。哪种方法最好从中检索默认值? - pkuderov