Nov 8, 2013

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

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

DECLARE @Var AS BIGINT = 25
SELECT SQL_VARIANT_PROPERTY(@Var,'BaseType')

Output: bigint

Creating IsBigint function in sql server

CREATE FUNCTION IsBigint (
    @Data AS SQL_VARIANT
)
RETURNS BIT
AS
BEGIN

    DECLARE @IsBigint AS BIT
   
    IF SQL_VARIANT_PROPERTY(@Data,'BaseType') = 'bigint'
         SET @IsBigint = 1
    ELSE
         SET @IsBigint = 0
        
    RETURN @IsBigint
END

Examples:

DECLARE @Var1 INTEGER = 25
DECLARE @Var2 VARCHAR(5) = '25'
DECLARE @Var3 BIT = 1
DECLARE @Var4 BIGINT = 25

SELECT
    dbo.IsBigint(@Var1),
    dbo.IsBigint(@Var2),
    dbo.IsBigint(@Var3),
    dbo.IsBigint(@Var4)

Output:
0   0   0   1

Note: In sql server there is not bigint constant. For example:

SELECT
    dbo.IsBigint(2147483647),
    dbo.IsBigint(21474836485555555555555555),
   
Output:
0   0

No comments:

Post a Comment