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

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

SELECT SQL_VARIANT_PROPERTY(25,'BaseType')

Output: int

Creating IsInteger function in sql server

CREATE FUNCTION IsInteger(

    @Data AS SQL_VARIANT

)

RETURNS BIT

AS

BEGIN

    DECLARE @IsInt AS BIT

   

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

         SET  @IsInt = 1

    ELSE

         SET  @IsInt = 0

        

    RETURN @IsInt

   

END

Examples:

DECLARE @Var1 INTEGER = 25

DECLARE @Var2 VARCHAR(5) = '25'

DECLARE @Var3 BIT = 1

DECLARE @Var4 BIGINT = 25

SELECT

    dbo.IsInteger(@Var1),

    dbo.IsInteger(@Var2),

    dbo.IsInteger(@Var3),

    dbo.IsInteger(@Var4)

Output:

1   0   0   0

Note: Maximum and minimum value of integer constant in sql server is 2147483647 and -2147483648 respectivily. Any number lager or smaller than this values are not integer constant. For example:

SELECT

    dbo.IsInteger(2147483647),

    dbo.IsInteger(-2147483648),

    dbo.IsInteger(2147483648),

    dbo.IsInteger(-2147483649)

Output:

1   1   0   0