CODEWITHSUNDEEP
sqlsql-server
BruceyBandit
BruceyBandit

Reputation: 4324

datatype for an iif statement

If in a SELECT statement there is the following line:

iif(fr.BoundID=0,'OutBound','InBound') as 'FlightBound'

Then when I perform a CREATE TABLE statement, should I include the actual datatype of the BoundID field which is a tinyint in the database table, or shall the datatype be varchar because I think (but not 100% sure looking at the existing code) that the previous person writing this code is saying display 'OutBound, InBound' if the ID is 0?

Upvotes: 2

Views: 490

Answers (2)

TT.
TT.

Reputation: 16137

The return type is the type in true_value and false_value with the highest precendence (reference, see return types)

Returns the data type with the highest precedence from the types in true_value and false_value. For more information, see Data Type Precedence (Transact-SQL).

Data type precendence here up to SQL Server 2016:

user-defined data types (highest)
sql_variant
xml
datetimeoffset
datetime2
datetime
smalldatetime
date
time
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar (including nvarchar(max) )
nchar
varchar (including varchar(max) )
char
varbinary (including varbinary(max) )
binary (lowest)

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

In your case it will be VARCHAR(8). You can always check metadata using sys.dm_exec_describe_first_result_set:

SELECT *
FROM sys.dm_exec_describe_first_result_set(
   'SELECT iif(BoundID=0,''OutBound'',''InBound'') as ''FlightBound''
   FROM #tab',NULL,0)

LiveDemo

What datatype to choose for new table TINYINT vs Textual representation is dependent on your business requirements. I would probably stay with TINYINT (search for lookup table named like BoundType or ask senior developer/architect).

Upvotes: 2

Related Questions