Simplify Your Stored Procedure Logic with Expectations
Written by Andrew Wilson
John Hugg was talking with me today about a way to reduce the complexity of error checking in a stored procedure and how rarely it is used. VoltDB’s stored procedures let you set “expectations” on each SQL statement. Those expectations can eliminate several lines of code leading to shorter, readable and more reliable stored procedures.
Consider the following sample:
LoginProc1 checks for a row count and returns either a 0 or a 1 if the username and password combination could not be found. LoginProc2 sets an expectation that the results ofvoltExecuteSQL() will return exactly one row, otherwise it will throw an exception and rollback the transaction.
This is the simplest example of an expectation. Consider that more typical procedures will have insert, update or delete operations as well and by using the expectations you are able to reduce or eliminate all the code that would check the success of each statement. The resulting code is shorter, more readable and more reliable as VoltDB is determining whether the outcome of the query is producing the appropriate number of rows. You can then extend the error checking to validate data or perform transformations knowing that your queries were successful.
If you would like to learn more but are not sure where to start, you can find documentation, guides and the latest code updates on our GitHub page.