SIGN is an in-build function of Transact-SQL. This is a mathematical function that returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.
Syntax
SIGN ( numeric_expression )
Example
DECLARE @value int
SET @value = -1
WHILE @value < 2
BEGIN
SELECT SIGN(@value)AS 'RETURN VALUE'
SELECT @value = @value + 1
END
Screen Shot
If data type is decimal
-- DATA TYPE DECIMAL
DECLARE @value DECIMAL(4,2)
SET @value = -1
WHILE @value < 2
BEGIN
SELECT SIGN(@value)AS 'RETURN VALUE'
SELECT @value = @value + 1
END
Screen Shot
Here we notice that when there is positive value the function gives positive values and if the values are negative it will return you negative values. Also we notice that if the data type is INT the return value is INT and when the value passed to the function is Numeric the result also matches it. Not every data type is compatible with this function. Here is the quick look up of the return types.
1. bigint -> bigint
2. int/smallint/tinyint -> int
3. money/smallmoney -> money
4. numeric/decimal -> numeric/decimal
5. everybody else -> float
Leave Comment