"Pause" as stroed proc?

[Niv3k]

Is there anyway to pause a stored procedure while waiting for user input? For example, I have a business rule that prohibits finalizing transactions that don't match the bill amount. But we have a new client company where they will accept $1 under or $500 over. Sometimes. We have to ask them, first.

Should I continue to skip over these transactions, record them in a temp table, and return the result set, or can I request some sort of user intervention when it comes across this particular scanario?

Kevin

 

[SemperFiDownUnda]

You could have the Stored proc go through its processing with 1 additional parameter to indicate a force through. If left empty (default the parameter to NULL) and the amount doesn't match (the business rule to allow completely automated processing) then return to the user a error that they can trap and ask the user if it should be pushed through. If it should be the stored proc is called agian filling in the parameter that will cause the process to be pushed through. To make it more robust I would have the Paramater as a INPUT OUTPUT parameter that is the value of the difference so you can show the person confirming the processing. Then pass this back into the SP. As long as the difference is the same as it was 5 seconds before the push it through.