Earlier this year I posted a series of posts about prime numbers to demonstrate coding principles and techniques. As recursion is typically a very efficient approach and in my last post on the subject I stated that we could create a stored procedure to call our stored procedure recursion that pulls from our function recursion. Of course as a parent procedure that procedure would contribute to the maximum procedure nesting of 32. That approach would look something like this:
-- ============================================= -- Author: Thomas Marshall -- Create date: 4/23/2015 -- Description: -- ============================================= CREATE PROCEDURE iteratingParent3100PrimeTestCalls @max_iterations INT = 0, @demo_override INT = 2 AS BEGIN SET NOCOUNT ON; DECLARE @n INT = 1; WHILE @n <= @max_iterations BEGIN EXECUTE [dbo].[thirtyTwoHundredPrimalityTests] @demo_override --2 SET @n += 1; END END GO
With the default value for the max iterations parameter nothing will happen. If we pass the demo override parameter a value of 1 or less we will get an error because our procedure will nest more than 32 levels. As you may know iteration is dramatically less efficient than recursion. If you didn’t already guess from the title of this post we are going to try and get more out of the recursive part. What’s great is just how easy it is.
First we are going to make a variation on our recursive CTE function. You’ll notice the only difference from the hundred more prime tests function besides the name is that we restrict our CTE to 2 to the power of 15 rows. It’s important to note that the query inside the function will throw an error without OPTION (MAXRECURSION 23767) and the create statement will error with it. It’s tangential to note that (2^15)-1 is the largest signed 16 bit integer and the zip code for Paisley Florida.
CREATE FUNCTION [dbo].[sixteenBitsMorePrimeTests](@n int) RETURNS TABLE AS RETURN ( WITH recurseMore AS ( SELECT @n AS p, 1 AS n UNION ALL SELECT p + 1 , n + 1 FROM recurseMore WHERE n < 32768 ) SELECT p, n FROM recurseMore WHERE dbo.intIsPrime(p) = 1 ) GO
Fortunately for us we can specify the OPTION (MAXRECURSION 32767) in the call to the procedure so we can avoid erring on the recursion.
-- ============================================= -- Author: Thomas W Marshall -- Create date: -- Description: -- ============================================= CREATE PROCEDURE [dbo].[thirtyTwoTimeTwoToThePowerOfSixteenPrimalityTests] @c INT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @n INT; SELECT @n = MAX(p) FROM dbo.primes; INSERT INTO dbo.primes SELECT p FROM dbo.[sixteenBitsMorePrimeTests](@n) OPTION (MAXRECURSION 32767); SET @n = @c + 1; IF @n < 32 EXEC thirtyTwoHundredPrimalityTests @n ; END GO
Now if we like we can have our new procedure called iteratively to keep inserting primes into our table. The hardest part should be naming the parent procedure.