Same code errors in a Stored Procedure, works in T-SQL-CodePudding

I have a stored procedure that calls a linked server like below. The column 'datestr' is of type char(8) and is not always properly formatted. It is usually yyyymmdd but since I do not control how that data is formatted, I am using the TRY_CAST to only get rows where I can format it into a date.

Executing the SP gives me the following error:

Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Running the exact same code extracted from the SP in T-SQL returns data without error. I'm certain the issue is with the part of the WHERE clause with the DATEADD function hitting a value that is not able to be CAST into a date but I can't figure out why it runs differently in SP and extracted T-SQL.

I checked the plan using SET SHOWPLAN_ALL ON before running both and see some variations. Namely the estimated rows in the working query are much lower in the Remote Query operator (~200K vs. 15 mil)

CREATE Procedure [dbo].[SampleSP]
AS
    SELECT top 50 tbl1.rowID as rowID, 
            year(datestr) as [year],
            month(datestr) as [month],
            count(*) AS CountRow
    FROM   [LinkedSer].[RemoteDB].[dbo].[tbl1] tbl1
        inner join [dbo].[LocalTbl] tbl2 on tbl1.rowID = tbl2.rowID
    WHERE  tbl1.row_type = 'tbl1A' 
    and (TRY_CAST(tbl1.datestr AS date) IS NOT NULL 
        and tbl1.datestr > DATEADD(yy, -10, getdate()))
    group BY tbl1.rowID, year(tbl1.datestr), month(tbl1.datestr)

CodePudding user response：

The order the predicates are evaluated is plan-dependent. So you need to eliminate the potentially-invalid comparison from your code.

And simplifying to:

 and TRY_CAST(tbl1.datestr AS date) > DATEADD(yy, -10, getdate())

should do the trick.