Use default value of a column in stored procedures

Question

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

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?

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

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?

No, I don't. The column `value` in `t2` has a default value (`1` in the example). I want to use that default value instead of hardcode `1` in the stored procedure because if I change the default value to `0` I don't want to change each stored procedure. — CrApHeR, May 11 '15 at 15:22
I don't understand the query then - you're wanting to take `t2.value` unless it's `NULL`, and if it's `NULL`, take... `t2.value`? That doesn't make any sense. — Siyual, May 11 '15 at 15:23
possible duplicate of [SQL Server Find out default value of a column with a query](http://stackoverflow.com/questions/3817885/sql-server-find-out-default-value-of-a-column-with-a-query) — Tanner, May 11 '15 at 15:24
@Siyual. The DEFAULT value of that column. You can see that in the `t2` table creation (value BIT DEFAULT 1) — CrApHeR, May 11 '15 at 15:26
You can use default keywork in insert statement, but for select see Tanners comment — Giorgi Nakeuri, May 11 '15 at 15:26
I think the comments already have explained it but if you use DEFAULT in your insert e.g. INSERT INTO t2 values (3, DEFAULT) and then change the ISNULL to ISNULL(t2.value, 0) then you will get the result you're looking for I thnk — Christian Barron, May 11 '15 at 15:30
I don't have the value in `t2` yet. I didn't insert any value and I don't want to insert it. I just need to get the Default column value in the ISNULL sentence. — CrApHeR, May 11 '15 at 15:33
`DECLARE @b BIT; SET @b = CASE WHEN '((1))' = '((1))' THEN 1 ELSE 0 END` Then use this variable in ISNULL function — Giorgi Nakeuri, May 11 '15 at 15:39
I edited my question why I can't use the post posted by @Tanner. — CrApHeR, May 11 '15 at 15:40
@CrApHeR, it is workaround for your problem. You have no choice — Giorgi Nakeuri, May 11 '15 at 15:48
Thanks @GiorgiNakeuri, I also can remove the parenthesis from the Default value but I would like to find a safe way to do it. I don't think that Microsoft didn't think about it. — CrApHeR, May 11 '15 at 15:52
@MartinSmith I agree with you. Then I have another question. Is there any way to get the Default Value after evaluate the Default string? I mean, does SQL Server have a way to evaluate a string to a Value? — CrApHeR, May 11 '15 at 17:56

score 1 · Accepted Answer · answered May 11 '15 at 17:57

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).

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

This method is safe because there is an evaluation in SQL Server engine that is returning the result and there is not string replacement. — CrApHeR, May 11 '15 at 18:04

score 0 · Answer 2 · answered May 11 '15 at 16:38

0

This works for me:

DECLARE @def as bit = null

SELECT @def 
UNION ALL
SELECT ISNULL(@def, REPLACE(REPLACE((
    SELECT COLUMN_DEFAULT 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = 'dbo' 
      AND TABLE_NAME = 'dual'
      AND COLUMN_NAME = 'tempo'),'(', ''), ')', '')) As def

answered May 11 '15 at 16:38

shA.t

16,580
5
54
111

I already commented this possibility (Replace the parenthesis) in the comment area of my question, but I am looking for a clean and safe way to get the value. – CrApHeR May 11 '15 at 16:54

Use default value of a column in stored procedures

2 Answers2

Linked