jrichards54 (10/28/2012)

---

Is it possible to use the "IF" function in a column at design time as the value for that field? I do it regularly in Excel with no problem but I am new to SQL Server and have been unable to figure this one out. I have been using 3 of my own books plus the Online Books but to no avail. If I cannot do it in design, can I create a script (or a stored proceedure) that will do it at run time? Thanks, JRichards54

ChrisM@home (10/28/2012)

---

SQL Server only permits constants as default values. ...

CREATE TABLE #Temp
    (ID INT IDENTITY
    ,Value1 CHAR(1) DEFAULT ('A')
    ,Value2 CHAR(1) DEFAULT (CASE WHEN GETDATE() > '2012-10-29' THEN 'B' END))

INSERT INTO #Temp (Value1)
SELECT NULL UNION ALL SELECT 'B'

SELECT * FROM #Temp

DROP TABLE #Temp

dwain.c (10/28/2012)

---

True but... you can construct an IF-like syntax using CASE as a default as long as you don't reference any column names in the CASE:

CREATE TABLE #Temp
    (ID INT IDENTITY
    ,Value1 CHAR(1) DEFAULT ('A')
    ,Value2 CHAR(1) DEFAULT (CASE WHEN GETDATE() > '2012-10-29' THEN 'B' END))

INSERT INTO #Temp (Value1)
SELECT NULL UNION ALL SELECT 'B'

SELECT * FROM #Temp

DROP TABLE #Temp