In a stored procedure we have a query that has worked in the past but no longer does on after we upgraded servers. The new server is SS2016. It still works on our older 2012SQL Server. Any type of options / settings that may have been that jump out at you? Or any reason at all whey we are suddenly encountering this.
WHERE g.GCN_SEQNO = drug.GCN_SEQNO
AND ISNUMERIC(drug.GCN_SEQNO) > 0
Note: the data from g.GCN_SEQNO is all numeric. The data in the drug.GCN_SEQNO is a char field because it has one value = 'UNKNOWN'. The rest of the data is numeric format.
When we changed to a CASE statement or Try_cast, it works fine. So we solved for it, but just not sure why its not an issue.
WHERE g.GCN_SEQNO = try_cast(drug.GCN_SEQNO as numeric(28,0))