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

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

DECLARE @Var AS TINYINT = 25

SELECT SQL_VARIANT_PROPERTY(@Var,'BaseType')

Output: tinyint

Creating IsTinyint function in sql server

CREATE FUNCTION IsTinyint (

    @Data AS SQL_VARIANT

)

RETURNS BIT

AS

BEGIN

    DECLARE @IsTinyint AS TINYINT

   

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

         SET  @IsTinyint = 1

    ELSE

         SET  @IsTinyint = 0

        

    RETURN @IsTinyint

END

Examples:

DECLARE @Var1 INTEGER = 25

DECLARE @Var2 TINYINT = 25

DECLARE @Var3 BIT = 1

DECLARE @Var4 BIGINT = 25

SELECT

    dbo.IsTinyint(@Var1),

    dbo.IsTinyint(@Var2),

    dbo.IsTinyint(@Var3),

    dbo.IsTinyint(@Var4)

Output:

0   1   0   0

Note: Number from 0 to 255 are not tinyint constants. They are integer constants. For example:

SELECT

    dbo.IsTinyint (0),

    dbo.IsTinyint(128),

    dbo.IsTinyint(255),

   

Output:

0   0   0