What would be a better way to handle this sql logic?

Question

Inside of a stored procedure, I populate a table of items (#Items). Just basic information about them. However for each item, I need to make sure I can sell them, and in order to do that I need to perform a whole lot of validation. In order to keep the stored procedure somewhat maintainable, I moved the logic into another stored procedure.

What would be the best way to call the stored procedure for each item in the temp table?

The way I have it now, I apply an identity column and then just do a while loop, executing the stored procedure for each row and inserting the validation result into a temporary table. (#Validation)

However now that logic has changed, and in between the creation of #Items and the execution of the loop, some records are deleted which screws up the while loop, since the Identity no longer equals the counter.

I could handle that by dropping the identity column and reapplying it before the while loop, but I was just wondering if there was a better way. Is there a way to get a specific row at an index if I apply an order by clause?

I know I could do a cursor, but those are a pain in the ass to me. Also performance is somewhat of a concern, would a fastforward readonly cursor be a better option than a while loop? The number of rows in the #Items table isn't that large, maybe 50 at most, but the stored procedure is going to be called quite frequently.

Answer 1

Does this kind of business logic really has to be in database? I don't know much about your scenario, but maybe it would be best to move that decision you're trying to model with SPs into the application?

So you might try to use a function instead of stored procedure for that logic, and include the result of this function as a column in your temporary table? Would that work for you? Or if you need the data in realtime every time you use it later, then function returning 0/1 values, included in select list, could be a good bet anyway

Answer 2

If it's possible to rewrite your stored procedure logic using a query, i. e. a set-based approach?

You should try this first.

Answer 3

1. Turn your validation stored procedure into a user defined function that accepts an item id or the data columns needed to validate an item record
2. Create the temp table
3. Insert all your items
4. Write a delete query for the temp table that calls your new UDF in the WHERE clause.

Answer 4

I agree that if you can do it set-based then do it that way. Perhaps put the validation into a user-defined function instead of a sproc to enable that. Which may pave the way for you to be able to do it set-based.

e.g.

SELECT * FROM SomeTable WHERE dbo.fnIsValid(dataitem1, dataitem2....) = 1

However, I know this is may not be possible depending on your exact scenario, so...

Correction edit based on now understanding the IDENTITY/loop issue: You can use ROW_NUMBER() in SQL 2005 to get the next row, doesn't matter if there are gaps in the IDENTITY field as this will assign a row number to each record ordered by what you tell it: -- Gets next record SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY IDField ASC) AS RowNo, * FROM #temptable ) s WHERE s.RowNo = @Counter