How to check a given data or column is type of bit or not in sql server?

Sql server doesn’t support a function like IsBit.  We can test a given data or column is type of BIT by using the function SQL_VARIANT_PROPERTY. For example:

DECLARE @Var AS BIT = 1

SELECT SQL_VARIANT_PROPERTY(@Var,'BaseType')

Output: bit

Creating IsBit function in sql server

CREATE FUNCTION IsBit(

    @Data AS SQL_VARIANT

)

RETURNS BIT

AS

BEGIN

    DECLARE @IsBit AS BIT

   

    IF SQL_VARIANT_PROPERTY(@Data,'BaseType') = 'bit'

         SET  @IsBit = 1

    ELSE

         SET  @IsBit = 0

        

    RETURN @IsBit

   

END

Examples:

DECLARE @Var1 BIT = 0

DECLARE @Var2 BIT = 'True'

DECLARE @Var3 INTEGER = 1

DECLARE @Var4 VARCHAR = 'True'

SELECT

    dbo.IsBit(@Var1),

    dbo.IsBit(@Var2),

    dbo.IsBit(@Var3),

    dbo.IsBit(@Var4)

Output:

1   1   0   0

Note: In sql server 0 and 1 are integer constants they are not a bit type constants. In the same way 'True' and 'False' are varchar constants not a bit constant. For example:

SELECT

    dbo.IsBit(1),

    dbo.IsBit(0),

    dbo.IsBit('True'),

    dbo.IsBit('False')

Output:

0   0   0   0