Nov 8, 2013

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   

No comments:

Post a Comment