THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Functions That Are Not Often Used: SIGN

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

 

 

Published Tuesday, February 12, 2008 3:46 PM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Stephen Moore said:

Reminds of older Oracle versions that didn't have a CASE statement.  (Not sure if more recent versions of Oracle have it fixed.)  The workaround was to combine the decode and sign functions.

case

 when x >= 100 then 'a'

 else 'b'

end

was written

decode(sign(x - 100), 0, 'a', 1, 'a', 'b')

If I recall correctly.  I do remember that it was a pain.

February 12, 2008 4:56 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement