I have a table that will return an Integer. Many times the below query will return multiple IDs.
DECLARE @ID INT;
SELECT @ID = Id
FROM MyTable
I then have to execute a pre made stored procedure that accepts that Id. The Stored Procedure is setup to take one Id at a time (I can't change it to make a list). How can I get the stored procedure to iterate over the entire list of IDs Returned?
exec storedProc @ID
You can try following
Add new column 'Processed' tinyint with default value as zero on your table, to hold processed flag (assume: 0 - New, 1 - Successfully processed, 2 - Error ). Iterate each unprocessed records where Processed=0 (new), get ID value and execute the stored procedure by passing the ID. After the execution of stored procedure, update processed as 1. If any error, set processed = 2.
DECLARE @ID INT;
SELECT TOP 1 @ID = Id FROM MyTable
WHERE Processed = 0
WHILE @@ROWCOUNT<>0
BEGIN
BEGIN TRY
EXEC storedProc @ID
UPDATE MyTable SET Processed = 1
WHERE ID = @ID
END TRY
BEGIN CATCH
SELECT @ID ID, ERROR_MESSAGE() as ErrMsg
UPDATE MyTable SET Processed = 2 -- Error
WHERE ID = @ID
END CATCH
-- Get Next record
SELECT TOP 1 @ID = Id FROM MyTable
WHERE Processed = 0
End -- While..end
