I'm currently trying to fix a bug in a stored procedure, where everyone once in a while (once a month) I will find a child record end up on the wrong parent account, due to a bug in a sync between two systems. Looking at the code I believe I have found the issue. The issue I am seeing is that within a loop, the parent account for each child record is set with a select statement, and should the query not return any results, the variables used to assign accountid and accountname will retain the information from the previous loop cycle. As to why the loop cycle can't find the associated record, that is another question entirely and an intermittent issue I have yet to solve. With this in mind though, I don't see this method of setting variables to be a great idea! Is there any way I can validate or confirm that a variable is set correctly, and if not, take that record out of consideration for processing? Any help, advice, or words of wisdom greatly appreciated!
Here is a code sample, that runs within a loop to set the variable. @id is the current child object id that will be processed during the loop cycle.
Select @AccountId = t1.AccountId,
@Account = t1.AccountName
From Accounts t1
Inner Join ChildTable t2 on t1.ID = t2.ID
Where t2.Id = @Id
The most likely problem is empty result set in the query. The easiest way to test for this is to validate the statement row count. You didn't specify your SQL flavor, but most (if not all) engines will have some method of testing the row count, for example,
@@ROWCOUNT in SQL Server.
Just use an
if statement to validate 1 row was effected by the query...