Why is coalesce converting my string to an int?

Question

I'm trying to use coalesce to give me the first non null phone number. The phone numbers are stored as nvarchar(50) strings. When I run the query, it gives an error stating "The conversion of the nvarchar value '3803333757' overflowed an int column. Maximum integer value exceeded."

		SELECT		phoneNum, fk_PhoneTypeID
		INTO		#phones
		FROM		tbl_FamilyPhone fp
		INNER JOIN	tbl_phone p ON fp.fk_phoneID = p.pk_PhoneID
		WHERE		fp.fk_FamilyID = '65243565-C5D0-4304-8389-1F1566337EBB'

		SELECT		COALESCE(Home, Work, Cell, Other)
		FROM		(
						SELECT		(SELECT null FROM #phones WHERE fk_phoneTypeID = 'A6F1688E-015B-481D-8C41-DCC1FEA5000') AS 'Home',
									(SELECT phoneNum FROM #phones WHERE fk_phoneTypeID = 'AFF72CE0-3533-4A4D-941C-015E6AA90000') AS 'Work',
									(SELECT phoneNum FROM #phones WHERE fk_phoneTypeID = 'AE60EAF4-F026-4CAE-8BBE-4B3AE3340000') AS 'Cell',
									(SELECT phoneNum FROM #phones WHERE fk_phoneTypeID = 'AE60EAF4-F026-4CAE-8BBE-4B3AE3340000') AS 'Other'
					) allPhones

		DROP TABLE	#phones

It seems to be assuming the hardcoded null is an int and therefore trying to convert subsequent arguments to int? But I don't understand why.

Answer 1

Do you have Phone Types table? It will be better to JOIN with that table and then pivot phone using type instead of the keys.

I also suggest to explicitly specify type using (cast null as varchar(20))  for the first case. Also, why do you store phones as nvarchar? Do you expect unicode phone numbers?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

What's the data in #phones table look like after running the SELECT...INTO? What specifically is the error? Are you absolutely sure you're getting the error on the second select? I ask because even if I force the scenario I think you're describing, I don't get the error you touched upon:

SELECT COALESCE(Home, Work, Cell, Other)
FROM (SELECT (SELECT null) AS 'Home',
(SELECT '2128675309') AS 'Work',
(SELECT '7105102604') AS 'Cell',
(SELECT '9233664122') AS 'Other'
) allPhones

Under certain situations, a NULL will translate to an integer data type. For example, take the following scenario:

SELECT null as col INTO #xr280xr;
EXEC tempdb..sp_help #xr280xr;

I have no idea why the engine acts in this fashion. I've never seen documentation on it, and frankly, I don't care (just being honest).

But, if you just want the first non-null value, why not use something like the following:

SELECT TOP 1 phoneNum
FROM tbl_FamilyPhone fp INNER JOIN	tbl_phone p
ON fp.fk_phoneID = p.pk_PhoneID
WHERE fp.fk_FamilyID = '65243565-C5D0-4304-8389-1F1566337EBB'
AND phoneNum IS NOT NULL;

I'm assuming a one to many relationship between tbl_FamilyPhone and tbl_phone. I'm assuming you want any non-null phone and order doesn't matter.

Good Luck Bro,
~CA

Answer 3

The hardcoded value NULL gets special handling in most cases.  In particular, SQL tries to make it the type that fits the expression it is in.  So if you do COALESCE(NULL, 'ABC'), that NULL will be treated as varchar(3), because 'ABC' is a character type.  And the result of the expression is varchar(3).  You can see this by running

Select Coalesce(Null, 'ABC') As foo Into #test
go
sp_help #test;
go
drop table #test;

In the same way, if you do COALESCE(NULL, 25.4), the result will be a numeric(3,1) because that is the type of the value 25.4.

But when you do

SELECT null FROM #phones WHERE fk_phoneTypeID = 'A6F1688E-015B-481D-8C41-DCC1FEA5000') AS 'Home'

Now, SQL does not have an expression to help it pick a datatype for that null.  But every column returned by a SELECT must have a type.  So SQL has to choose one.  The type it chooses when it has no help is int.  You can see this by running

Select Null As foo Into #test
go
sp_help #test;
go
drop table #test;

So the type of Home is int, the type of Work, Cell, and Other are all varchar.  But when you do COALESCE(Home, Work, Cell, Other), that's a COALESCE of the following types  COALESCE(int, varchar, varchar, varchar) and the datatype precedence forces the varchars to become int's and you get your error.

Naomi is correct, one way to fix this is

SELECT Cast(null as varchar(<whatever>) FROM #phones WHERE fk_phoneTypeID = 'A6F1688E-015B-481D-8C41-DCC1FEA5000') AS 'Home'

Then SQL won't treat it as an int.

Tom

Answer 4

Do you have Phone Types table? It will be better to JOIN with that table and then pivot phone using type instead of the keys.

I also suggest to explicitly specify type using (cast null as varchar(20))  for the first case. Also, why do you store phones as nvarchar? Do you expect unicode phone numbers?

My thinking originally was this would be easier for some developers, not familiar with PIVOT to read/maintain. I think any difference would be negligible when there are < 10 records involved wouldn't it? But now that I see the finished product, I'll try out the pivot version and see how it looks. Thanks for pointing that out.

The phones table is like 6-7 years old and I didn't create it. Not sure why it's nvarchar vs. varchar. I don't know of any phone formats that use unicode. We don't store formatted numbers, just digits, so it doesn't really make sense anyway.

What's the data in #phones table look like after running the SELECT...INTO? What specifically is the error? Are you absolutely sure you're getting the error on the second select? I ask because even if I force the scenario I think you're describing, I don't get the error you touched upon:

It looks like what I'd expect, string phone number, then a Guid ID. Not sure if you were asking something more specific. I don't know how to find what type SQL thinks each one is. The error is quoted in the original post and it is on the second, outer select, as a result of the hard-coded null. Not sure why you're getting a different result. I'm not using a query like the one you suggested because the order does matter. I want them in the order they are in the coalesce expression. Thanks for the suggestion though.

The hard-coded null was just something I threw in for testing that exposed this behavior. It won't be part of the final version so I won't need to cast it. Thanks for confirming it is choosing int for the type of null, Tom.