0

My application uses SQL Server 2014. It has a table called Report. I have the following stored procedure to delete records. 

create procedure delteReportsByIds
    @ids varchar(8000)
as 
begin
    exec ('delete from Report where id in ( '+ @ids +' )')
end

The data passed to ids is something like 

1,4,6

which deletes 3 records with ID being 1, 4, 6. Here is the problem. Now the table is very large and ID of a record has reached 7 digits, something like

1380025

So the stored procedure can only delete a max 1000 records (with 7 digit IDs) at one time. It seems that I am unable to increase the 8000 limit. How can I delete 2000 records with the above procedure at one time?

curious1
  • 14,155
  • 37
  • 130
  • 231
  • 5
    This is where you want a [User Defined Table Type](https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017) – Dale K May 23 '19 at 21:37
  • 3
    For your specific scenario, you can try `VARCHAR( MAX )`. It holds up to 2GB of data. But I personally try to avoid dynamic SQL, especially in simple scenarios like yours. Either @DaleBurrell suggestion or [String Split](https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) function to parse input and join the result to the table. – Alex May 23 '19 at 21:56
  • @Alex, if you make your comment as an answer, I will accept it. Thanks! – curious1 May 24 '19 at 00:07
  • 2
    If your query does not fit in 8000 characters, going to `MAX` is not necessarily going to help you much. There is a (soft) limit to how big a query can get before the parser/optimizer will run out of resources and refuse to process it, and you'll hit this long before you hit the theoretical maximum limit on the query text length. This is especially true for `IN`, because internally this is translated to a big `OR` clause. Once that limit is reached, you'll have to switch to something that does scale (and a TVP is a good candidate for that). – Jeroen Mostert May 24 '19 at 11:22
  • Jeroen, thanks for the additional information! Very helpful. – curious1 May 24 '19 at 11:49

1 Answers1

1

For your specific scenario, you can try VARCHAR( MAX ). It holds up to 2GB of data. But I personally try to avoid dynamic SQL, especially in simple scenarios like yours. Either @DaleBurrell suggestion in comments or String Split function to parse input and join the result to the table.

Alex
  • 4,885
  • 3
  • 19
  • 39