How to execute sp number of times based on multiple output from another sp

Question

I have sp as below

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

I need to execute EXEC pDeleteTbl 'PatAssess',@IPAsOrgID,@IPAsOrgGrpID,@PAsID,@IPAsPatID

but the request (select splitdata From @output)returns more than one value, therefore, how to execute the pDeleteTbl request ... thanks in advance

Answer 1

you need to use the cursor for this

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