Nov 5, 2013

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

No comments:

Post a Comment