Rewriting of delete SQL script.

There is no such obvious limit. The temporary table can have more user names, and the number of deleted rows depends on the amount of rows with matching names.

As Qlemo mentioned, your current script doesn't have any option available to delete only 999 records as it can delete all eligible records.
Can you kindly clarify the below as I got some doubts from your question..
1. Why fldUserName was declared as nvarchar(max) if it is going to just store your UserName.. Username fields ideally won't exceed 20/25 characters at all.

fldUserName nvarchar(max)

2. Your INSERT into #UserList query is missing the values.. Kindly clarify whether you meant to insert only 999 records to this table.

insert into #UserList (fldUserName) values

>Is there a method of rewriting this so I am not limited to deleting 999 rows at one time?
There would be if this line is somewhere above your code ... SET ROWCOUNT 999

Otherwise no.  I've had multiple clients where I've seen this in an archive/delete job where the ultimate goal is to delete a large subset of the table (loop, cursor) and minimize locking/blocking (SET ROWCOUNT) at the same time.

>fldUserName nvarchar(max)
As a total aside, what values are typically inserted into this column?  I don't know too many user names that have so many characters that would require an nvarchar(max), n unicode yes, max no, so it appears this column is defined excessively large, which would slow down all queries.

The code you provided has no LIMIT statement, so unsure why you imagine your code limits to 999 rows.

Suggestion: If your code really does LIMIT to 999 rows, this relates to your input data matching 999 rows, rather than your SQL.

Said another way, only your starting data limits rows acted on, not your SQL code.

Hi David/Jim/Qlemo/Raja,

My apologies for not getting back sooner internet connection issues. To clarify my question yes the limit occurs on the input data values insert inbetween the lines

insert into #UserList (fldUserName) values

('asdfsa')
('gwerq') --The manual insert errors. 
etc

DELETE from tblPERMS_Incidents where Username_Incidents collate SQL_Latin1_General_CP1_CI_AS in (select fldUserName from #UserList)

Select allOpen in new window

When I put any more that 999 lines I get the error message,

Thank you

By the way, kindly clarify whether these 1000+ values that are inserted are same across all scenarios or vary..
If it varies, kindly try inserting/storing all those values to a temp table for easier handling instead of writing those 1000+ values in the INSERT INTO clause..

Thank you kindly to all of you that assisted.