CODEWITHSUNDEEP
c#sqlasp.netsql-serverstored-procedures
User987
User987

Reputation: 3823

Batch delete operation procedure not working

I have a stored procedure which looks like following:

alter procedure [dbo].[zsp_deleteEndedItems]
(
@ItemIDList nvarchar(max)
)
as
delete from 
SearchedUserItems
WHERE EXISTS (SELECT 1 FROM dbo.SplitStringProduction(@ItemIDList,',') S1 WHERE ItemID=S1.val)

The parameter IDList is passed like following:

124125125,125125125...etc etc

And the split string function look like following:

ALTER FUNCTION [dbo].[SplitStringProduction]
(
  @string nvarchar(max),
  @delimiter nvarchar(5)
) RETURNS @t TABLE
(
  val nvarchar(500)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@string,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(500)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

This is supposed to delete all items from table "SearcheduserItems" under the IDs:

124125125 and 125125125

But for some reason after I do a select to check it out:

select * from SearchedUserItems
where itemid in('124125125','125125125')

The records are still there...

What am I doing wrong here? Can someone help me out?

Upvotes: 0

Views: 117

Answers (1)

Thom A
Thom A

Reputation: 95554

As mentioned in the comments, a different option would be to use a table type parameter. This makes a couple of assumptions (some commented), however, should get you on the right path:

CREATE TYPE dbo.IDList AS TABLE (ItemID int NOT NULL); --Assumed int datatype;
GO

ALTER PROC dbo.zsp_deleteEndedItems @ItemIDList dbo.IDList READONLY AS

    DELETE SUI
    FROM dbo.SearchedUserItems SUI
         JOIN @ItemIDList IDL ON SUI.ItemID = IDL.ItemID;
GO

--Example of usage

DECLARE @ItemList dbo.IDList;

INSERT INTO @ItemList
VALUES(123456),(123457),(123458);

EXEC dbo.zsp_deleteEndedItems @ItemList;

GO

In regards to the question of an inline table value function, one such example is the below, which I quickly wrote up, that provides a tally table of the next 1000 numbers:

CREATE FUNCTION dbo.NextThousand (@Start int)
RETURNS TABLE
AS RETURN

    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)
    )
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 + @Start AS I
    FROM N N1 --10
         CROSS JOIN N N2 --100
         CROSS JOIN N N3; --1,000
GO

The important thing about an iTVF is that it has only one statement, and that is the RETURN statement. Declaring the table as a return type variable, inserting data into it, and returning that variable turns it into a multi-line TVF; which perform far slower.

Upvotes: 1

Related Questions