My previous post regarding this MySQL fascination was a fairly straight forward set of conditions that dynamically built a query and then executed it. This was great but I also had to make sure a valid batch id number was being passed to the query. To make this stored procedure resilient I made it so that one could hypothetically enter all null parameters and it would still work. A broken stored procedure is really difficult to debug.
So the following is a condition that first checks to see if a batch was inputed. If not, a select query goes out and finds an appropriate batch. If none is found an arbitrary number is entered. The cool part about this little new thing is that I could have a user defined variable get assigned a value within an isolated query within a stored procedure. Here is the code:
IF inputBatch IS NOT NULL THEN SET @query = CONCAT(@query, " WHERE BatchId = ", inputBatch); ELSE SET @batchQuery = CONCAT("SELECT InsertIndex FROM BatchTable WHERE BatchName = ", inputTable, " ORDER BY DataTimestamp DESC LIMIT 1 INTO @tempBatch"); IF debug = 1 THEN SELECT @batchQuery; SELECT @tempBatch; END IF; PREPARE pst FROM @batchQuery; EXECUTE pst; DEALLOCATE PREPARE pst; IF debug = 1 THEN SELECT @batchQuery; SELECT @tempBatch; END IF; IF @tempBatch IS NULL THEN SET @tempBatch = '1'; END IF; SET @query = CONCAT(@query, " WHERE BatchId = ", @tempBatch); END IF;
The debug helps a lot when running this in the command line. Anywhere else and the output of the procedure is malformed.