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