Search code examples
sql-serverstringnullbit

How do i return bit value from a select statement when a column value in the statement is null

In a select query one of the columns is a nullable varchar column. How does one return a 1 or 0 as a bit value if that specific column is NULL?

I have tried doing something like this

select
    isnull(nullif(isnull(JavascriptCode, 0),JavascriptCode), 1) as HasJavaScriptCode
from code

but that seems to return a string value so i cant use in my code.

Solution

  • Use CASE:

    SELECT CAST(CASE WHEN JavascriptCode IS NULL THEN 0 ELSE 1 END As bit) As HasJavaScriptCode
FROM code

    Since 2012 version (which is currently the oldest supported version) SQL Server supports IIF - which can be used instead of simply case expressions to create a more concise code:

    SELECT CAST(IIF(JavascriptCode IS NULL, 0,1) As bit) As HasJavaScriptCode
FROM code