sql - 如何根据另一个sp的多次输出执行一个sp多次

Question

我有一个 sp，如下所示

ALTER PROCEDURE [dbo].[pPatAssessDel] 
    @IAllGUIDs nvarchar(max) ,
     @IPAsPatID UNIQUEIDENTIFIER,
     @IPAsOrgID UNIQUEIDENTIFIER,
     @IPAsOrgGrpID  UNIQUEIDENTIFIER

AS


declare @output TABLE(splitdata NVARCHAR(MAX) )

  DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(',', @IAllGUIDs) 
    WHILE @start < LEN(@IAllGUIDs) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@IAllGUIDs) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@IAllGUIDs, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(',', @IAllGUIDs, @start)

    END 
  Declare @PAsID NVARCHAR(MAX)
  Set @PAsID =(select splitdata  From @output)

EXEC pDeleteTbl 'PatAssess',@IPAsOrgID,@IPAsOrgGrpID,@PAsID,@IPAsPatID

Delete From PatAssess where PAsRowGUID in (select splitdata  From @output) 
AND PAsPatID = @IPAsPatID

我需要执行
EXEC pDeleteTbl 'PatAssess',@IPAsOrgID,@IPAsOrgGrpID,@PAsID,@IPAsPatID

但是查询(select splitdata From @output) 返回多个值，那么如何执行 pDeleteTbl 查询......提前致谢

Answer 1

你必须使用光标

ALTER PROCEDURE [dbo].[pPatAssessDel] 
    @IAllGUIDs nvarchar(max) ,
     @IPAsPatID UNIQUEIDENTIFIER,
     @IPAsOrgID UNIQUEIDENTIFIER,
     @IPAsOrgGrpID  UNIQUEIDENTIFIER

AS






declare @output TABLE(splitdata NVARCHAR(MAX) )

  DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(',', @IAllGUIDs) 
    WHILE @start < LEN(@IAllGUIDs) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@IAllGUIDs) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@IAllGUIDs, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(',', @IAllGUIDs, @start)

    END 
declare @IPAsID nvarchar(max)
declare cur CURSOR LOCAL for
    select splitdata  From @output

open cur

fetch next from cur into @IPAsID

while @@FETCH_STATUS = 0 BEGIN


    EXEC pDeleteTbl 'PatAssess',@IPAsOrgID,@IPAsOrgGrpID,@IPAsID,@IPAsPatID

    fetch next from cur into @IPAsID
END

close cur
deallocate cur  

Delete From PatAssess where PAsRowGUID in (select splitdata  From @output) 
AND PAsPatID = @IPAsPatID