SQL Server Stored Procedure oddity

Question

Ok, so we have a stored procedure that has been in production for at least 3 years and working find. Last night the oddest thing happened. We have tried to normalize data about people, so we have one table that contains all ids a person may have in other systems we integrate with. This table has a unique idtypeid lookup and a unique keyname. This we have a view of the id number table linked to the idtype table so that you can search for a desired element.

The result for any given person may vary, but there is only one results per type. However, since not all ids are created equal, we use a NVARCHAR type for the id value. All results for this code started to fail late last night. The value we are looking for is a INT. So a sample of our code looks like:

BEGIN

declare

@personId int = 12345,

@idPerson BIGINT;

select @idPerson = IdNumber

from [Person].[vwPersonIdNumber]

where PersonId= @personId

AND IdKeyName = 'SPECIFIC ID'

END

The failure messages were that could not convert a varchar value to a BIGINT. For some reason, it was pulling other values from table that had a different keyname... Our fix was to use the IdTypeId directly, but this is not our practice... Alternately we could change the @idPerson to VARCHAR(100) and it would work as well. But there is a singular result for this query and it was returning a totally different row. It is almost like the where clause was ignoring the KeyName check completely. Does anyone have any idea what happened...

Answer 1

Yes, this is a common theme, which has its root cause in that SQL has implicit conversion between strings and numbers in differences to many other statically typed languages. If that had been forbidden, you would have had to tackle the situation from the start.

The query above works fine as long as the query plan first filters for the IdKeyName, and wait with converting IdNumber until later. However, the optimizer may decide to perform the conversion before the filtering, and in that case the query fails. You could argue that this behaviour is incorrect, but it is not unique to SQL Server. I recall having seen a blog post about a similar behaviour in Oracle.

The correct solution is to use an explicit convert:

select @idPerson = try_convert(bigint, IdNumber)

It is important to use try_convert, which returns NULL when conversion fails.

Answer 2

Hi @Mark Gerow

The failure messages were that could not convert a varchar value to a BIGINT.

Normally, this error appears when the data in the column (IdNumber) contains non-numeric values (like: #, ^.etc) or values that cannot be implicitly converted to BIGINT.

Try this query to identify any non-numeric values in the IdNumber column.

SELECT IdNumber
FROM [Person].[vwPersonIdNumber]
WHERE PersonId = @personId
AND IdKeyName = 'SPECIFIC ID'
AND TRY_CAST(IdNumber AS BIGINT) IS NULL;

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".