1

I have a procedure that I send a string param into it with this structure 10,11,12.

And I want to split it with comma (,) and delete records in the table that contains these ids.

My procedure:

ALTER PROCEDURE [dbo].[deleteDeal]
    @orderIds varchar(100)
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM deal 
    WHERE OrderId IN (SELECT OrderId 
                      FROM STRING_SPLIT(@orderIds, ','));
END

But this code deletes all rows from the deal table.

How can I do this action?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hasan Cheraghi
  • 867
  • 2
  • 11
  • 26
  • 3
    Much better to send IDs in using a table valued parameter. Anyway, just debug your code outside of a SP, and change the delete to a select while you are debugging. – Dale K Aug 31 '22 at 05:35
  • good idea, I will try with send table param – Hasan Cheraghi Aug 31 '22 at 05:37
  • similar questions asked multiple times on SO – Mitch Wheat Aug 31 '22 at 05:58
  • 1
    when you select from a STRING_SPLIT you have to query for the column `value` in stead of OrderID. As it stands your query will result in an exception `Invalid column name 'OrderId'` – GuidoG Aug 31 '22 at 05:59

1 Answers1

1

By suggestion, I create a table type

CREATE TYPE [dbo].[deleteDealType] AS TABLE(
    [OrderId] [bigint] 
)

and then change my procedure to

ALTER PROCEDURE [dbo].[deleteDeal]
    @orderIds deleteDealType readonly
AS
BEGIN

    SET NOCOUNT ON;

    DELETE FROM deal WHERE OrderId IN (SELECT OrderId FROM @orderIds);
END

and works perfectly

Dale K
  • 25,246
  • 15
  • 42
  • 71
Hasan Cheraghi
  • 867
  • 2
  • 11
  • 26