0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
iwells
  • 1
  • Things like this are extremely rarely bugs in the system; millions of people build and run dynamic queries every day. It would be hard to even know where to start without the full SQL statement. Are you able to edit that into the question? Can you guarantee that your dataset hasn't changed? Have you tried clearing the DB cache and/or restarting the SQL service? – Cᴏʀʏ Aug 29 '17 at 20:55
  • My best guess is that during your dynamic sql composing, some bad value is encountered, such as a null value and that could invalidate your dynamic sql. – jyao Aug 29 '17 at 20:58
  • I can guarantee the dataset hasn't changed and no I haven't restarted the service or cleared the cache. – iwells Aug 30 '17 at 11:18
  • I can guarantee the dataset hasn't changed and no I haven't restarted the service or cleared the cache. The statement is huge wouldn't make sense, but if I do SET @SQL = big statement it doesn't work, but if I literally chop it in half with NO other modifications and do SET SQL_A = half of my statement, SET SQL_B = other half of the statement and SET SQL_C = SQL_A + SQL_B it works ... then if I SET SQL = SQL_C it works again ... I just can't figure out what's there causing the issue. – iwells Aug 30 '17 at 11:21
  • Also if I erase half of my statement (the dynamic query is a union of 2 selects) either union will work on its own, but not together ... not a union issue because of the previous comment about SQL_C = SQL_A + SQL_B working ... if I make the last 5 or 6 columns in the second query = '' then it bypasses the case statement ... seems like its a length or some error in processing the case. – iwells Aug 30 '17 at 11:25

0 Answers0