I have a sproc that has these lines
DECLARE @newTimestamp datetime = CURRENT_TIMESTAMP UPDATE dbo.Accounts SET Modified = @newTimestamp
However, in the Accounts DB, we have this
[Modified] DATETIME2 (7) NOT NULL,
And then whenever we do comparisons (particular for equality), we don't get the desired answers. We have worked around this by setting the compatibility level low enough (110) to not trip on this, but is there any easy way to find any instances where we're mismatching data types in our sprocs vs our tables?
I'd rather not have to write a parser, but it seems like if I had to, I'd trawl through every table definition and record the column types, then find every use of each column and try to determine if it's being set or compared to a variable declared as a different type.
Are there any tools out there that already do this, or is there a complaint somewhere in a system view or log?
I've found that Brent Ozar's sp_blitzcache can probably find this in the query plans, but there's no guarantee that the cache will have every instance of this for a given DB; I think I'd feel more comfortable with a static analysis of the code instead of a live look at a running SQL server. I've got access to (many) live copies of the DBs, as well as the source code for the SQL solution in Visual Studio.
Edit - not really relevant to my question, but I can give the background. When we have a datetime, it has 3 digits accuracy after the decimal point. When we have a datetime2, it has 7 digits accuracy. If you take a single timestamp, and try to compare a datetime and a datetime2 of the same moment, they won't be equal.
If we fix our compatibility level to 110, it ignores the extra accuracy of the datetime2, which is our current behavior.