在存储过程中使用列的默认值 [英] Use default value of a column in stored procedures

问题描述

我正在使用 SQL Server 2012 并且我有 2 个表具有以下定义

I am using SQL Server 2012 and I have 2 tables with the following definition

CREATE TABLE t1 (id INT PRIMARY KEY, value NVARCHAR(10))
CREATE TABLE t2 (id INT PRIMARY KEY, value BIT DEFAULT 1)

ALTER TABLE t2 WITH CHECK ADD CONSTRAINT FK FOREIGN KEY(id) REFERENCES t1 (id)

我为当前示例插入了以下列:

I inserted the following columns for the current example:

INSERT INTO t1 VALUES (1, 'a')
INSERT INTO t1 VALUES (2, 'b')
INSERT INTO t1 VALUES (3, 'c')

INSERT INTO t2 VALUES (1, 1)
INSERT INTO t2 VALUES (3, 0)

我正在运行这个查询并且它有效

I am running this query and it works

SELECT
  t1.*, ISNULL(t2.value, 1)
FROM
  t1
  LEFT JOIN t2 ON t1.id = t2.id

有没有办法用我在列中定义的默认值替换这部分ISNULL(t2.value, 1)中的1表中的值 t2?

Is there any way to replace the 1 in this part ISNULL(t2.value, 1) with the default value that I have defined in the column value in the table t2?

这是我用这个例子创建的 sqlfiddle:SQLFIDDLE DEMO

Here is the sqlfiddle I created with this example: SQLFIDDLE DEMO

更新 1:
我不能使用 SQL Server:使用查询找出列的默认值，因为它返回 ((1)) 而我无法将 ((1)) 转换为 位.有什么办法可以解决吗?

UPDATE 1:
I can't use SQL Server: Find out default value of a column with a query because it returns ((1)) and I can't cast ((1)) to BIT. Is there any way to fix that?

推荐答案

您没有按照预期的方式使用默认设置.这是 SQL Server 在插入时内部评估的东西(如果使用 default 关键字，则可能会更新).

You are not using the default in the manner it is intended. It is something SQL Server evaluates internally at time of insert (or potentially update if the default keyword is used).

它不适用于 SELECT.考虑到它可以包含任意表达式，例如 DEFAULT CAST(GETDATE() AS INT) % 2 或调用标量 UDF.从字符串转换为位不会为您评估这些表达式.

It is not intended for use in SELECT. Consider that it can contain arbitrary expressions such as DEFAULT CAST(GETDATE() AS INT) % 2 or calling a Scalar UDF. Casting from string to bit won't evaluate those expressions for you.

你能做这样的事情的唯一方法是单独评估它

The only way you could do something like this would be to evaluate it separately

DECLARE @B            BIT
        , @Definition NVARCHAR(max)

SELECT @Definition = N'SELECT @B = '
                     + object_definition(default_object_id)
FROM   sys.columns
WHERE  NAME = 'value'
       AND object_id = OBJECT_ID('dbo.t2')

EXEC sys.sp_executesql
  @Definition,
  N'@B BIT OUTPUT',
  @B = @B OUTPUT

SELECT t1.*,
       ISNULL(t2.value, @B)
FROM   t1
       LEFT JOIN t2
              ON t1.id = t2.id 

这篇关于在存储过程中使用列的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！