I have this MSSQL query I need to debug the functioning of a stored procedure that runs every 10 minutes.

                FROM [Main].[dbo].Listing cs 
                WHERE expired | missing = 0
                AND cs.id NOT IN (SELECT id FROM [Main].[dbo].ListingSaved) 
                ORDER BY dateCheck DESC

I have this query, but the problem is that if the TOP(10) entries are inside ListingSaved, it will return null. Is there a way to make it non-deterministic and get the next 10 values not inside ListingSaved every time we call this query?

New contributor
online is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 2
    Edit the question and provide a minimal reproducible example, i.e. the CREATE statements of the tables and/or other objects involved (paste the text, don't use images, don't link to external sites), INSERT statements for sample data (dito) and the desired result with that sample data in tabular text format.
    – sticky bit
    Jan 12 at 1:07
  • 3
    Your premise "the problem is that if the TOP(10) entries are inside ListingSaved, it will return null" is incorrect. Your predicate cs.id NOT IN ... filters out the rows first, then of the remaining rows (respecting the other predicates), the top 10 sorted by dateCheckdescending are returned. If you're currently getting no rows back then that means no rows in your Listing table match all of the criteria you've specified in your predicates. You can prove what I'm saying is true by removing the TOP(10) keyword from your query, and will see no rows returned still.
    – J.D.
    Jan 12 at 2:37
  • 3
    I'd also question the use of DISTINCT *. If the table has a unique key then this serves no purpose, and if it does not then you need to ask why not, and why duplicates are wanted. WHERE expired | missing = 0 might look cool, but it's not sarge-able, instead do WHERE (expired = 0 OR missing = 0) which at least might hit an index Jan 12 at 3:02

Your Answer

online is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.