CODEWITHSUNDEEP
c#sqlsql-server-2008stored-proceduressql-delete
Caress Castañares
Caress Castañares

Reputation: 73

Deleting multiple rows based on row number(s)

I want to delete rows according to the row number passed in from a WinForm. Of course, the output of the ROW_NUMBER() calculation will always update when you delete a row, so row number will change too, therefore I want to do this in one shot rather than a loop.

I have this code:

CREATE PROCEDURE Delete_Record (@RowNum INT)
AS     
  ;WITH REC_ROW AS
  (
    SELECT *, ROW_NUMBER() OVER (ORDER BY [Record Number]) AS RN
        FROM User_Data
  )
  DELETE
    FROM REC_ROW
    WHERE RN IN (@RowNum)

And when I type:

 exec Delete_Record @RowNum = '1,2'

It produces the error:

Error converting data type varchar to int.

If I change @RowNum INT to @RowNum varchar(max), it will just produce the error message:

Error converting data type varchar to bigint.

When I hard-code the values:

;WITH REC_ROW AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY [Record Number]) AS RN
        FROM User_Data
)
DELETE
  FROM REC_ROW
  WHERE RN IN (1,2)

It will successfully delete the row 1 and 2. The question is how can I integrate this in a stored procedure and input the '1,2' to pass to the IN clause?

Upvotes: 3

Views: 3167

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

If you are going to be passing more complex strings than 1,2 you may want to consider using a table-valued parameter (TVP).

CREATE TYPE dbo.Integers AS TABLE
(
  RowNumber INT PRIMARY KEY
);

Now you can create your stored procedure and pass in a DataTable or other collection from your application without ever bothering with constructing comma-separated strings or trying to break them apart.

CREATE PROCEDURE dbo.Delete_Record
  @RowNums dbo.Integers READONLY
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH REC_ROW AS (...your CTE unchanged here...)
  DELETE REC_ROW
   FROM REC_ROW INNER JOIN @RowNums AS r
   ON r.RowNumber = REC_ROW.RN;
END
GO

If you really want to use a split function:

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE WITH SCHEMABINDING 
AS
  RETURN 
  (  
    SELECT Item = y.i.value('(./text())[1]', 'int')
    FROM ( SELECT x = CONVERT(XML, '<i>' 
        + REPLACE(@List, @Delimiter, '</i><i>') 
        + '</i>').query('.')) AS a 
    CROSS APPLY x.nodes('i') AS y(i));
GO

Now your stored procedure:

CREATE PROCEDURE dbo.Delete_Record -- always use a schema prefix!
  @RowNums VARCHAR(MAX)
AS 
BEGIN
  SET NOCOUNT ON;

  ;WITH REC_ROW AS
  (
    SELECT *, ROW_NUMBER() OVER (ORDER BY [Record Number]) AS RN
        FROM dbo.User_Data
  )
  DELETE REC_ROW
    FROM REC_ROW 
    INNER JOIN dbo.SplitInts(@RowNums, ',') AS r
    ON r.Item = REC_ROW.RN;
END
GO

But I guarantee you the TVP will perform better.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

This expression does not do what you think it does:

 WHERE RN IN (@RowNum)

It is looking for cases where @RowNum is '1,2'. And this causes the problem, because '1,2' cannot be converted to an integer. You have declared the parameter an integer when you define the procedure, but then call it with a string.

You can do what you want with:

WHERE ','+@RowNum+',' like '%,'+RN+',%'

The extra commas ensure that "1" doesn't match "10".

EDIT:

There are essentially three approaches (that I can think of) for handling a string of comma delimited integers as real integers (which is better because the query will use integers).

One is to use the split() function, which returns a table of the elements of a delimited string. You can google for various implementations of it.

The second is to use dynamic SQL. This is fine for queries that do not need to be instantaneous. It can incur additional overhead in compiling the query, which may be noticeable in transactional systems.

The third is to use recursive CTEs to parse the string. I would probably use this method for a stored procedure.

Upvotes: 0

Related Questions