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