Windows Server 2008

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