parrotsnest parrotsnest - 1 year ago 33
SQL Question

Best practice for setting variables with select in a loop

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

Thanks for your input everyone. Everyone has supplied valuable feedback, and I apologize for not providing more of the script which would allow a more exact solution. What I decided to go with is to check the tables that will be used in variable assignment, to confirm the account data is present, and only add those child records to the collection to loop through. There is still a concern of data changing from point a (adding to the collection) to point b (variable assignment), but I will look to control the tables somehow, as these tables are not extremely active, and I for the most part have the only access to them. Thanks again!

Answer Source

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...