0

I have a stored procedure. In it, there is an inline select that UNIONs several tables. If I highlight the code within the SP and execute it, it runs fine and returns 10 million rows.

If I copy the highlighted code to a new query window and execute that, it returns only about 55000 rows (the same number of rows that the first select in the inline view returns).

Can someone explain what is going on?

SELECT COUNT(*) FROM 
(
    SELECT SequenceId, comment_text [Text], comment_end_date
        FROM dart_comment
UNION 
    SELECT SequenceId, adj_comment [Text], report_date comment_end_date
    FROM dart_labor_detail 
    WHERE adj_user_id != null  AND adj_user_id != 'None'
UNION
    SELECT SequenceId, adj_comment [Text], report_date comment_end_date
    FROM dart_suspend_detail 
    WHERE adj_id != null  AND adj_id != 'None'
UNION
    SELECT SequenceId, adj_comment [Text], report_date comment_end_date
    FROM dart_labor_history 
    WHERE adj_user_id != null AND adj_user_id != 'None'
UNION
    SELECT SequenceId, adj_comment [Text], report_date comment_end_date
    FROM dart_suspend_history 
    WHERE adj_id != null AND adj_id != 'None'
)

The only difference is highlighting the code inside the SP, and copying (and highlighting) the code in a new query window (tied to the same database as the SP).

Keith Bogart
  • 1
  • Keith, at first replace `adj_... != null` with `not adj_... is null` – DimaSUN Oct 25 '19 at 18:30
  • Nothing is `=` to `NULL`, including `NULL`. Nothing, also, is `!=` to `NULL`. If you are performing `NULL` comparisons you have to use `IS NULL` and `IS NOT NULL`. an expression `{expr} =/!= NULL` will return `Unknown`; which for a `WHERE` clause isn't true and so the row will not be returned. – Thom A Oct 25 '19 at 18:31
  • See also: https://stackoverflow.com/questions/9766717/in-sql-server-what-does-set-ansi-nulls-on-mean – Jacob H Oct 25 '19 at 19:09
  • Thank you, Dima; once I replaced != null with IS NOT null, it worked fine in both windows. – Keith Bogart Oct 25 '19 at 19:51

0 Answers0