SIGN is one of those function which you almost never see used in code.
Sometimes you are asked by the front-end/middle-tier developers to return a rowcount as well with the result set.
However the developers want you to return 1 if there are rows and 0 if there are none. How do you do such a thing?
Well I am going to show you two ways. the first way is by using CASE and @@ROWCOUNT, the second way is by using the SIGN function
For CASE we will do this
RETURN CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
So that's pretty simple, if @@ROWCOUNT is greater than 0 return 1 for everything else return 0
Using the SIGN function is even easier, all you have to do is this
RETURN SIGN(@@ROWCOUNT)
That's all, SIGN Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. In this case -1 is not possible but the other two values are
So let's see this in action
USE pubs
GO
--Case Proc
CREATE
PROCEDURE TestReturnValues
@au_id
VARCHAR(49) ='172-32-1176'
AS
SELECT
*
FROM
authors
WHERE
au_id =@au_id
RETURN
CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
GO
--Sign Proc
CREATE
PROCEDURE TestReturnValues2
@au_id
VARCHAR(49) ='172-32-1176'
AS
SELECT
*
FROM
authors
WHERE
au_id =@au_id
RETURN
SIGN(@@ROWCOUNT)
GO
--Case Proc, 1 will be returned; default value is used
DECLARE
@Rowcount int
EXEC
@Rowcount = TestReturnValues
SELECT
@Rowcount
GO
--Case Proc, 0 will be returned; dummy value is used
DECLARE
@Rowcount int
EXEC
@Rowcount = TestReturnValues 'ABC'
SELECT
@Rowcount
GO
--Sign Proc, 1 will be returned; default value is used
DECLARE
@Rowcount int
EXEC
@Rowcount = TestReturnValues2
SELECT
@Rowcount
GO
--Sign Proc, 0 will be returned; dummy value is used
DECLARE
@Rowcount int
EXEC
@Rowcount = TestReturnValues2 'ABC'
SELECT
@Rowcount
GO
--Help the environment by recycling ;-)
DROP
PROCEDURE TestReturnValues2,TestReturnValues
GO
What else can we do with SIGN? How about if we want to indicate with 0 and 1 if there is a row in a table when we do an outer join, if there is a row we want to display 1 and otherwise 0
CREATE
TABLE #Table1 (ID INT)
INSERT
INTO #Table1 VALUES (1)
INSERT
INTO #Table1 VALUES (2)
INSERT
INTO #Table1 VALUES (3)
INSERT
INTO #Table1 VALUES (3)
CREATE
TABLE #Table2 (ID INT)
INSERT
INTO #Table2 VALUES (1)
INSERT
INTO #Table2 VALUES (3)
INSERT
INTO #Table1 VALUES (3)
You can do this easily with CASE
SELECT
CASE WHEN t2.ID IS NULL THEN 0 ELSE 1 END AS HasValue ,t2.ID FROM #Table1 t1
LEFT JOIN #Table2 t2 on t1.id = t2.id
1 1
0 NULL
1 3
When using SIGN you also need to use COALESCE because SIGN with a NULL value will return NULL
SELECT
SIGN(COALESCE(t2.ID,0)) AS HasValue,t2.ID
FROM
#Table1 t1
LEFT
JOIN #Table2 t2 on t1.id = t2.id
1 1
0 NULL
1 3
What happens when we insert negative values?
INSERT
INTO #Table1 VALUES (-3)
INSERT
INTO #Table2 VALUES (-3)
SELECT CASE WHEN t2.ID IS NULL THEN 0 ELSE 1 END AS HasValue ,t2.ID FROM #Table1 t1
LEFT
JOIN #Table2 t2 on t1.id = t2.id
1 1
0 NULL
1 3
1 -3
SELECT SIGN(COALESCE(t2.ID,0)) AS HasValue,t2.ID
FROM
#Table1 t1
LEFT
JOIN #Table2 t2 on t1.id = t2.id
1 1
0 NULL
1 3
-1 -3 -- oops
As you can see HasValue is now -1 when using SIGN. We can use the ABS function to help us out.
SELECT
SIGN(ABS(COALESCE(t2.ID,0))) AS HasValue,t2.ID
FROM
#Table1 t1
LEFT
JOIN #Table2 t2 on t1.id = t2.id
1 1
0 NULL
1 3
1 -3
The only problem with using the SIGN function is that a person reading the code might not know what the code is supposed to do.
Between these two statements it is much more obvious what the second statement does.
SIGN
(COALESCE(t2.ID,0))
CASE
WHEN t2.ID IS NULL THEN 0 ELSE 1 END AS HasValue
I leave it up to you to decide if you should use the SIGN function, if you ever see it used in code at least you will know what it does.
Here is what the execution plan looks like, the top one is the CASE statement
|--Compute Scalar(DEFINE:([Expr1002]=If ([t2].[ID]=NULL) then 0 else 1))
|--Compute Scalar(DEFINE:([Expr1002]=sign(If ([t2].[ID]<>NULL) then [t2].[ID] else 0)))
I did use the SIGN function on this blog a while back, remember the post below?
C# IsNullOrEmpty Function In SQL Server