SQL Server: query with many fields returning null; query with many fields commented out returning expected data [closed]

Question

This question is concerning a "large" stored procedure in SQL Server.

There is an issue with one of the SSRS reports I administer, which I have tracked down to the main stored procedure that pulls data for the report. The process the stored procedure follows is a little convoluted, but it seems to mostly work. That "mostly" is a problem, though.

We keep a JSON representation of the data behind the report in the database. The stored procedure calls a "known good" stored procedure to get the data. And then, because of the structure of the JSON, a series of CTEs parse the JSON down into a bunch of entities (questions and answers), which are then inserted into a temp table. Finally, the stored procedure uses a select statement of the form:

select ( select IsSelected
           from #QuestionsAndAnswersTranspose
          where QuestionKeyName = 'Foo'
            and AnswerKeyName = 'Baz'
       ) QuestionFooBazIsSelected
     , ( select AnswerText
           from #QuestionsAndAnswersTranspose
          where QuestionKeyName = 'Foo'
            and AnswerKeyName = 'Foo Comments'
       ) as QuestionFooCommentsAnswerText
-- and inserts these into another temp table:
into #QuestionsAndAnswers

to turn each row in #QuestionsAndAnswersTranspose into the single field value the SSRS report cares about (for that row).

There are many, many such subqueries (approximately 500). I understand that is a lot of columns, but the documentation does say that SQL Server can handle tables with up to 1024 columns and select statements with up to 4096 columns.

Now, here is the problematic behavior this query is exhibiting. I recently got a bug report that some of the fields are coming in as null. I made a simplified version of the select statement that pulled the static JSON out of the database, parsed it with the same CTEs (which I copied into my script) and then selected the 5 specific columns (I copied and pasted the subqueries to ensure no typoes are relevant). The shortened version of the script produced the expected values, while the longer script returns null.

TL;DR: query with many subqueries is returning null values in some subqueries; simplified query with same subqueries and on same data is returning expected values.

What could be wrong here?

Answer 1

NULL can be a bit tricky to route out but looking at your code, I can see that NULL handling is not implemented as a general rule, which means NULL parsing errors could slip in through a lack of diligence.

Name = "Dave" is obviously fine, so in the instance above, there's no need to handle the NULL...

Name <> "Dave" needs nulls handling in SQL, this is because while Name = NULL is understandably equal to Name <> "Dave" in most mental logic.

As a SQL default, Name <> "Dave" will not return the NULL records.

This might not seem like a problem but it is... because one NOT clause, or not Equal clause needs NULL handling or you will risk dropping data in an early part of your process which should appear later on in your process.

This is the first and most obvious consideration when seeing nulls coming through when you know the data is fine.

Trace the route, see if there is any inverse logic that doesn't handle nulls.

"NOT" is the gotcha statement.

Once you have established it is not NULL handling, the next thing to look for is "sudden data strictness" this is usually when your DB is case insensitive but your app is case sensitive. Suddenly your having to explicitly "make case" usually upper, in order to handle a case sensitivity issue which is making data that could be found, NULL return.

The last one is processes which pad strings with blank text, coming through in stricter fields than expected. Varchar(10) becoming Char(10) and you finding padded blank spaces in the string. Which normally wouldn't be a problem in SQL but there can be some funky side effects depending on how you are querying the data.

Those are some avenues to look down.

Check for unhandled pad spacing Negation in query routes without null handling. Case sensitivity when there should be none.

Get a known "supposed to work" example, if you don't have one, create one in test and follow the flow of the program code through the process, if you cannot, you may have to refactor the program so you can.