IsNull Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IsNull Question

Author

Topic

TallOne
Starting Member

49 Posts

Posted - 2008-04-09 : 17:01:04

Hi All,

Quick question...

why doesn't this work....
select IsNULL(select null),'TallOne')

But this does...
select IsNull((Select null),0)

How does it know what datatype I'm trying to get?

TallOne
Starting Member

49 Posts

Posted - 2008-04-09 : 17:08:12

Ooopps I left out a left parentheses....

select IsNULL((select null),'TallOne')

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-09 : 17:12:53

select ISNULL(null,'TallOne')

I prefer this though:

select COALESCE(null,'TallOne')

elsasoft.org

TallOne
Starting Member

49 Posts

Posted - 2008-04-09 : 17:17:17

Hi jezemine,

COALESCE still produces the same issue....
select COALESCE((select null),'TallOne')
Conversion failed when converting the varchar value 'TallOne' to data type int.

I left the (Select Null) to immitate the results of a subquery I'm performing...

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-09 : 17:51:26

you can't put a subquery there. show the entire sql please.

elsasoft.org

TallOne
Starting Member

49 Posts

Posted - 2008-04-09 : 18:07:34

Here ya go...
Below is output params in a sp....

Select @RelWO = isnull(wo.workorderid,-1),
@RelCRMEmail = isnull(t.email, 'DOES NOT EXISTS'),
@PrimaryRepEmail = IsNull((SELECT email FROM tuser tu WHERE TUserID = c.PrimaryRep),0),
@SecondaryRepEmail = IsNull((SELECT email FROM tuser tu WHERE TUserID = c.SecondaryRep),0)
from workorder wo inner join tuser tu On wo.TUserID = tu.TUserID
INNER JOIN Company c on wo.COID = c.COID
where ticketnum = @ItemNum

My subquerys in question are "SELECT email FROM tuser tu WHERE TUserID = c.xxxxx"

Thanks for your help!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-09 : 18:13:06

email column is of varchar type and 0 is of int type. both isnull parameters have to be of same type:
... IsNull((SELECT email FROM tuser tu WHERE TUserID = c.PrimaryRep), ''),...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-09 : 18:14:46

what I said was wrong - you can put a subquery there. just convert it to the datatype of the default value:

select COALESCE(convert(varchar(10),(select null)),'TallOne')

elsasoft.org

TallOne
Starting Member

49 Posts

Posted - 2008-04-10 : 08:48:01

Thanks for the responses! The convert works. But still my original question... :) How does SQL Server know what data type? Should I always assume that with COALESCE and ISNULL the default is int and always explicitly convert?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 08:52:54

quote:
Should I always assume that with COALESCE and ISNULL the default is int and always explicitly convert?

No.
Coalesce() returns value with data type same as all expressions which means all expressions must be of same data type

IsNull returns value with data type same as data type of first expression

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

TallOne
Starting Member

49 Posts

Posted - 2008-04-10 : 09:11:50

If you run the two queries below in query analyzer, you'll see that SQL Server is assuming a datatype of something because there is no underlying data type in "Select Null". It's assuming data type int. If we can't agree on that I need more explanation and an example.

In the case of replacing the "Select NULL" with a real subquery that also returns null, SQL Server is still assuming data type of int when in fact, the subquery, when not null will return varchar datatype! So, from what I'm seeing, SQL Server has no idea what datatype a null value should have been and assumes the type of int. Therefore, unless I'm just thick headed this week and don't understand, I'm always assuming that I need to convert an isnull/coalesce value to the data type desired if other than int. Right? Sorry to beat this subject to death, but it's not the first time I've encounter this and was just looking for a standard assumption. :(

Select ISNULL((Select null),'TallOne')
Select ISNULL((Select null),0)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-10 : 09:20:04

yes it assumes int because it's the first type in type precedence.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

TallOne
Starting Member

49 Posts

Posted - 2008-04-10 : 10:00:10

Ah! I'll research more on type precedence.

Thanks Everyone!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-10 : 11:48:39

quote:
Originally posted by TallOne

Therefore, unless I'm just thick headed this week and don't understand, I'm always assuming that I need to convert an isnull/coalesce value to the data type desired if other than int. Right?

No, that is not true. Under "normal" circumstances you data would be typed and SQL can figure out what to do with it. For example:

DECLARE @Yak TABLE(ID INT, Email VARCHAR(50))
INSERT @Yak
SELECT 1, 'Foo@foo.com'

SELECT COALESCE((SELECT Email FROM @Yak WHERE ID = 5), 'Unknown')
SELECT COALESCE((SELECT Email FROM @Yak WHERE ID = 5), 0)

By looking at the table definition sql can tell that the sub-query should return a data type of VARCHAR(50). Presumably, the second query is converting "0" to the varchar, but I would consider that code to be bad form. You should know what datatypes you are dealing with and act accordingly. If I saw someone COALESCEing a VARCHAR and an INT in a code review, I'd have to give them smack down. :)

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-10 : 12:56:35

quote:
Originally posted by Lamprey
If I saw someone COALESCEing a VARCHAR and an INT in a code review, I'd have to give them smack down. :)

You have got to be from the NYC Metro Area

Go Giants

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-10 : 14:07:47

quote:
Originally posted by X002548

quote:
Originally posted by Lamprey
If I saw someone COALESCEing a VARCHAR and an INT in a code review, I'd have to give them smack down. :)

You have got to be from the NYC Metro Area

Go Giants

Brett

8-)

Actually, I'm from Seattle, so I get to take a break from my normally passive-aggressive lifestyle and be the "Gatekeeper" (read as "data curmudgeon") in the database world. :)

TallOne
Starting Member

49 Posts

Posted - 2008-04-11 : 11:08:09

Thanks for the clarification guys! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources