--拼接 生成约束的语句 的一部分代码
declare @REF_MODE varchar(500)
SELECT @REF_MODE=
CASE WHEN T1.CONSTRAINT_TYPE = 'R' THEN
CASE WHEN DELETE_REFERENTIAL_ACTION = 1 THEN ' ([b]) REFERENCES test.ODC_ENCRYPTFIELD(FIELD_ID) ON DELETE '+DELETE_REFERENTIAL_ACTION_DESC+ CASE WHEN UPDATE_REFERENTIAL_ACTION = 1 THEN ' ON UPDATE '+UPDATE_REFERENTIAL_ACTION_DESC ELSE '' END
ELSE CASE WHEN UPDATE_REFERENTIAL_ACTION = 1 THEN '([b]) REFERENCES coffer.ODC_ENCRYPTFIELD(FIELD_ID) ON UPDATE '+UPDATE_REFERENTIAL_ACTION_DESC ELSE '' END
END
ELSE ' (b) ' END
FROM [test].[Func_GetColumnConstraints](schema名称,表名称) T1 --查询约束信息的函数
WHERE COLUNMNAME = 'b'
AND CONSTRAINTNAME='FK_TEST';运行会报如下错误:消息 245,级别 16,状态 1,第 2 行
在将 nvarchar 值 ' ([b]) REFERENCES coffer.ODC_ENCRYPTFIELD(FIELD_ID) ON DELETE CASCADE' 转换成数据类型 tinyint 时失败。不明白什么原因,求指教
declare @REF_MODE varchar(500)
SELECT @REF_MODE=
CASE WHEN T1.CONSTRAINT_TYPE = 'R' THEN
CASE WHEN DELETE_REFERENTIAL_ACTION = 1 THEN ' ([b]) REFERENCES test.ODC_ENCRYPTFIELD(FIELD_ID) ON DELETE '+DELETE_REFERENTIAL_ACTION_DESC+ CASE WHEN UPDATE_REFERENTIAL_ACTION = 1 THEN ' ON UPDATE '+UPDATE_REFERENTIAL_ACTION_DESC ELSE '' END
ELSE CASE WHEN UPDATE_REFERENTIAL_ACTION = 1 THEN '([b]) REFERENCES coffer.ODC_ENCRYPTFIELD(FIELD_ID) ON UPDATE '+UPDATE_REFERENTIAL_ACTION_DESC ELSE '' END
END
ELSE ' (b) ' END
FROM [test].[Func_GetColumnConstraints](schema名称,表名称) T1 --查询约束信息的函数
WHERE COLUNMNAME = 'b'
AND CONSTRAINTNAME='FK_TEST';运行会报如下错误:消息 245,级别 16,状态 1,第 2 行
在将 nvarchar 值 ' ([b]) REFERENCES coffer.ODC_ENCRYPTFIELD(FIELD_ID) ON DELETE CASCADE' 转换成数据类型 tinyint 时失败。不明白什么原因,求指教
declare @a int
set @a=3 --这个位置 1 和 3 都不报错,改成2 就会报错了
select case when @a=1 then 1
when @a=2 then 'b'
else 3 end
case when 要求类型统一
--拼接 生成约束的语句 的一部分代码
declare @REF_MODE varchar(500)
SELECT @REF_MODE=
(
CASE WHEN DELETE_REFERENTIAL_ACTION = 1 THEN
' ([b]) REFERENCES test.ODC_ENCRYPTFIELD(FIELD_ID) ON DELETE '+DELETE_REFERENTIAL_ACTION_DESC+ CASE WHEN UPDATE_REFERENTIAL_ACTION = 1 THEN ' ON UPDATE '+UPDATE_REFERENTIAL_ACTION_DESC
WHEN UPDATE_REFERENTIAL_ACTION = 1 THEN
' ([b]) REFERENCES coffer.ODC_ENCRYPTFIELD(FIELD_ID) ON UPDATE '+UPDATE_REFERENTIAL_ACTION_DESC
ELSE ' (b) ' END
)
FROM [test].[Func_GetColumnConstraints](schema名称,表名称) T1 --查询约束信息的函数
WHERE COLUNMNAME = 'b' and T1.CONSTRAINT_TYPE = 'R'
AND CONSTRAINTNAME='FK_TEST';declare @REF_MODE varchar(500)
SELECT @REF_MODE=
(
CASE WHEN T1.CONSTRAINT_TYPE = 'R' and DELETE_REFERENTIAL_ACTION = 1 THEN
' ([b]) REFERENCES test.ODC_ENCRYPTFIELD(FIELD_ID) ON DELETE '+DELETE_REFERENTIAL_ACTION_DESC+ CASE WHEN UPDATE_REFERENTIAL_ACTION = 1 THEN ' ON UPDATE '+UPDATE_REFERENTIAL_ACTION_DESC
WHEN T1.CONSTRAINT_TYPE = 'R' and UPDATE_REFERENTIAL_ACTION = 1 THEN
' ([b]) REFERENCES coffer.ODC_ENCRYPTFIELD(FIELD_ID) ON UPDATE '+UPDATE_REFERENTIAL_ACTION_DESC
ELSE ' (b) ' END
)
FROM [test].[Func_GetColumnConstraints](schema名称,表名称) T1 --查询约束信息的函数
WHERE COLUNMNAME = 'b'
AND CONSTRAINTNAME='FK_TEST';
不累吗?写好看点不行?
找到原因了,原因是UPDATE_REFERENTIAL_ACTION_DESC本来应该为‘cascade’之类的,但实际得到的是UPDATE_REFERENTIAL_ACTION的值数值1.
谢谢