TSQL: Run stored procedure on multiple IDs (deletion example)

tsql-run-stored-procedure-on-multiple-ids-deletion-example.sql

BEGIN TRANSACTION

	DECLARE @reviewMode int = '0'; 
	DECLARE @password nvarchar(max) = 'password_here';
	DECLARE @testStudyIDList NVARCHAR(MAX) = '2791,2793,2843,2844';
	DECLARE @testStudyID NVARCHAR(4000);
	DECLARE @TestStudyIDTable TABLE (ID NVARCHAR(4000));
	INSERT INTO @TestStudyIDTable(ID)
	SELECT Data FROM dbo.Split(@testStudyIDList, ',');

	DECLARE @RC int; DECLARE @studyID int; 
	DECLARE cur CURSOR FOR SELECT ID FROM @TestStudyIDTable;
	OPEN cur;
		FETCH NEXT FROM cur INTO @testStudyID;

		WHILE @@FETCH_STATUS = 0
		BEGIN
		   EXECUTE @RC = [dbo].[DeleteAllStudyData] 
		      @testStudyID, @reviewMode, @password;   
		   FETCH NEXT FROM cur INTO @testStudyID;
		END;
	CLOSE cur;
	DEALLOCATE cur;

ROLLBACK;

GO