sp_QuickieStore: Mixing @query_text_search and @query_text_search_not created primary key violations in #query_text_search

[ReeceGoding]

Version of the script 4.5.

What is the current behavior? When using both @query_text_search and @query_text_search_not, I sometimes get a runtime error reporting primary key violations in #query_text_search . The bad query is allegedly this one.

If the current behavior is a bug, please provide the steps to reproduce. The trick seems to be to put similar text in both, e.g. sp_quickiestore @query_text_search = 'username', @query_text_search_not = 'Users'

What is the expected behavior? No errors to be thrown and for the output to only include queries that match @query_text_search but don't match @query_text_search_not.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures? It's new code.

IMPORTANT: If you're going to contribute code, please read the contributing guide first. https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md Will do.

[ReeceGoding]

My general impression from reading the code is that the two parameters simply aren't compatible. They both share the same column of the #query_text_search temp table. I have never made any study of this stored procedure, but it seems obvious that the two parameters won't work together if the list of included queries and the list of excluded queries have both been placed in the same column of the same table.

Perhaps we need to make a #query_text_search_not temp table and EXCEPT-out everything that it shares with the #query_text_search temp table?

[erikdarlingdata]

@ReeceGoding yeah, this just needs another temp table for the not search. I’ll fix it.