CODEWITHSUNDEEP
sqlsql-serversql-server-2012
CrApHeR
CrApHeR

Reputation: 2655

Use default value of a column in stored procedures

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?

Upvotes: 2

Views: 2263

Answers (2)

Martin Smith
Martin Smith

Reputation: 453766

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

Upvotes: 1

shA.t
shA.t

Reputation: 16968

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

Upvotes: 0

Related Questions