I know there are lots of "odd behavior" questions on here, but I do think this is unique and I'm wondering if anyone has encountered this before?
I'm building a large dynamic query in a single nvarchar variable, @SQL, and then executing it ... it's a LARGE statement. I've run this many times for many weeks in the past and it works. Then today, something either corrupted or is stored in memory somewhere.
I execute the stored procedure and I get an error and I notice when I select @sql that my dynamic query skips a portion of a case statement in the dynamic code (very specifically after the 6th WHERE) and resumes about 10 lines later in the dynamic query code. I troubleshoot for a long time, but no luck. Finally I say, hmm what happens if I split the dynamic query up in to 2 parts, @SQL_A and @SQL_B and then SET @SQL_C = @SQL_A and @SQL_B and supply that ... sure enough, it works.
I know I'm the only person working on this because its local on my PC and while I won't 100% say nothing has changed I've checked this 100 times and can concatenate the statement from 2 strings to 1 and it works, but if I try and form it as 1 full @sql nvarchar statement it seems like there's some type of error occurring. The clause it fails on is WHEN ''GBAN07'' THEN 7 however if I remove this it keeps failing until I end the case statement after only 6 WHERE clauses. If I do this, it works correctly and doesn't skip the 10 lines of code ....
Thoughts?