Bugs in the 'DELETE without WHERE clause' warning
GavinAshford
Posts: 2 New member
in SQL Prompt
Hi, I'm pretty sure these are bugs but I wasn't 100% sure how they should reported on the forum, so apologies if this isn't suitable...
First things first, I'm currently using SQL Prompt 9.5.6.10002 on SSMS v18.0 (15.0.18118.0) against a SQL 2016 server (13.0.5264.1)
The first issue I have is that there doesn't seem to be a warning generated when declaring and then deleting from user-defined table type variables.
For example I have a user table type called BrandList and this code does not cause the warning prompt to appear.
First things first, I'm currently using SQL Prompt 9.5.6.10002 on SSMS v18.0 (15.0.18118.0) against a SQL 2016 server (13.0.5264.1)
The first issue I have is that there doesn't seem to be a warning generated when declaring and then deleting from user-defined table type variables.
For example I have a user table type called BrandList and this code does not cause the warning prompt to appear.
DECLARE @brandList BrandList
DELETE FROM @brandList
My second issue feels like a weird edge case.
It seems to be due to where the query text selection is when selecting and executing a sub-section of script, specifically when the selected DELETE query follows after a previous query that is indented in some way.
The warning prompt does appear but the name of the object being referenced in the prompt gets its first 1 or 2 characters truncated.
This is probably best shown with a set of screenshots...
I am using a simple test table for these: CREATE TABLE dbo.test (ID INT)
Finally, however, when combining these two issues it actually causes the first issue (not prompting on user-defined table type variables) to then begin warning about the DELETE without WHERE, but only when selecting in-line with the previous statement.
My second issue feels like a weird edge case.
It seems to be due to where the query text selection is when selecting and executing a sub-section of script, specifically when the selected DELETE query follows after a previous query that is indented in some way.
The warning prompt does appear but the name of the object being referenced in the prompt gets its first 1 or 2 characters truncated.
This is probably best shown with a set of screenshots...
I am using a simple test table for these: CREATE TABLE dbo.test (ID INT)
Finally, however, when combining these two issues it actually causes the first issue (not prompting on user-defined table type variables) to then begin warning about the DELETE without WHERE, but only when selecting in-line with the previous statement.
Tagged:
Answers
For the first point - the functionality wasn't designed to work for temp tables or table variables. This is described in the release notes when it was implemented https://documentation.red-gate.com/sp9/release-notes-and-other-versions/sql-prompt-7-2-release-notes which say:
The second issue is quite odd and I've not been able to reproduce it in Prompt 9.5.6 with SSMS 18 when indenting the select and blank line above the DELETE statement.
I can see why, in your case with the issue occurring, it would cause the table variable to start being alerted on, because it's removing the @ (or would remove the # for a temp table) so that it was no longer identified as that and would then be alerted on.
Do you have anything else in the query window and/or if you just put the SELECT and DELETE in a new blank query window does it still occur? Is it spaces or tabs and what options do you have in the style related to this. If you are able, it looks like you are supported and I could get the export of the Prompt.settings and the .sqlpromptstylev2 for your style as well via a support ticket.
Kind regards,
Alex
Have you visited our Help Center?
That'll be me not realising/reading-up on the designed function wouldn't work on temp/variable tables - apologies for that!
The examples above were all run in a blank/new query tab (no more code above).
I used spaces in my examples, but tab results in the same issue but tab always truncates 2 characters in the popup warning (even a single tab).
The important part for re-creation, for me, is where the text selection is highlighted before executing the selected part of the script - it has to be just the indented character on the blank line:
... not the character at the far left of the blank line:
I can submit a support ticket (even though its far from high priority) with the settings and styles files. Do you need me to put a specific subject so it can be found easily?
I've reached out with a support ticket to gather the files and as stated in it, even only selecting the space after the indent, it still doesn't cause the issue for me. We'll see if the files help with the reproduction!
Kind regards,
Alex
Have you visited our Help Center?