0

I have a stored procedure that will delete from ausers table based on an ID I pass in. If I was to delete multiple users or a range of the IDs, is there a way i can do this?

The IDs are Ints.

Chris Birch
  • 41
  • 2
  • 7
  • You can pass the start and end id and delete the range, or pass in a comma separated string and use the `IN` clause or pass in a xml formatted list of Ids – peeyush singh Dec 13 '18 at 08:57
  • you can (1) pass in a table parameter or a (2) csv of ids and split it in the sp. or (3) pass in a json or xml string of ids – g2server Dec 13 '18 at 08:57
  • 2
    Which dbms are you using? (Many products have their own versions of stored procedures.) – jarlh Dec 13 '18 at 09:01
  • Using Microsoft management studio. When seperate the ints with a common i get a "Error converting data type varchar to int." message – Chris Birch Dec 13 '18 at 09:04
  • 1
    Possible duplicate of [T-SQL stored procedure that accepts multiple Id values](https://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values) – EzLo Dec 13 '18 at 09:06
  • You can use collection or array of int and pass it – YLG Dec 13 '18 at 09:25

1 Answers1

0

You have to create one function which is used to split string and you can pass parameter with separated comma

Function
---------------
CREATE FUNCTION SplitString
(    
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

Store Procedure
----------------------
DECLARE @CustomerID VARCHAR(50) = '1,2,3'

DELETE FROM Customer WHERE CustomerID in (SELECT Item
FROM dbo.SplitString(@CustomerID, ','))

Hope this help you :)

Mustufa
  • 116
  • 4