Как передать больше значений для одного параметра в хранимой процедуре в SQL Server

Question

МОЙ текст:-

CREATE proc usp_delete    
        @tranid int           
        as 
        begin 
    delete from customer where tranid in(@tranid)
     end

Примечание:- Я хочу удалить записи более 1 записи с помощью этой хранимой процедуры, например:- если я пропущу 1,2,3,4,5,6, то все 6 записей должны быть удалены

Answer 1

Ты можешь использовать table-valued parameter для этого вам нужно заявить, что

CREATE TYPE EntityId AS TABLE  
( Id INT )
 GO

CREATE PROCEDURE usp_delete    
    @tranid EntityId READONLY
AS 
 BEGIN 
     DELETE c
     FROM customer c
     JOIN @tranid t ON t.Id=c.tranid 
 END

Для выполнения с TVP объявите переменную типа и передайте ее хранимой процедуре.

 DECLARE @entityId EntityId 

 INSERT INTO @entityId 
 VALUES(1),(2),(3),(4),(5)

 EXEC usp_delete @entityId 

Answer 2

В более поздней версии SQL Server вы можете использовать STRING_SPLIT для преобразования списка с разделителями в отдельные значения, а затем присоединиться к вашей таблице с помощью CROSS APPLY.

CREATE PROC usp_delete @tranid VARCHAR(MAX) AS
BEGIN
    DELETE c
    FROM   customer c
    CROSS APPLY STRING_SPLIT(@tranid, ',')
    WHERE c.tranid = value
END
GO
EXEC usp_delete '2,3,4'

В старой версии вы можете написать свою собственную табличную функцию для преобразования строки с разделителями в набор результатов.

CREATE function [dbo].[delimited_string_to_table] (
     @delimiter char(1)
   , @string    text
)
returns @table table (
     seqnbr  int           not null
   , string  varchar(255)  not null
)
as
begin

   declare @pos      int
   declare @textpos  int
   declare @chunklen smallint
   declare @tmpstr   varchar(8000)
   declare @leftover varchar(8000)
   declare @tmpval   varchar(8000)
   declare @seq int = 1

   set @textpos = 1
   set @leftover = ''

   while @textpos <= datalength(@string)
   begin

      set @chunklen = 8000 - datalength(@leftover) / 2
      set @tmpstr = @leftover + substring(@string, @textpos, @chunklen)
      set @textpos = @textpos + @chunklen

      set @pos = charindex(@delimiter, @tmpstr)

      while @pos > 0
      begin
         set @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
         insert @table (seqnbr, string) values(@seq, @tmpval)
         set @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
         set @pos = charindex(@delimiter, @tmpstr)
         set @seq = @seq + 1
      end

      set @leftover = @tmpstr

   end

   if len(rtrim(ltrim(@leftover))) != 0
      insert @table(seqnbr, string) values (@seq, ltrim(rtrim(@leftover)))

   return

end
GO

Что изменит пример, чтобы он выглядел так:

CREATE PROC usp_delete @tranid VARCHAR(MAX) AS
BEGIN
    DELETE c
    FROM   customer c
    JOIN   dbo.delimited_string_to_table(',', @tranid)
           ON c.tranid = string
END
GO