Stored Procedure: Part II

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.

This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Stored Procedure: Part II