Using CTE instead of CURSOR

Today, I came upon a need to change a Scalar Value Function in SQL 2005 that we are using to generate confirmation numbers based on an algorithm much like the following

Characters 1-5 will be the number of days since 1900.01.01; zero-padded if needed

Characters 6-10 will be the number of seconds since Today 00:00:00; zero-padded if needed

If this number is already in use, increment by 1 until reaching an unsed number

So I had two options to ensure that I properly checked for the numerically lowest available value.

1. Create a cursor incrementing a variable that stored the generated confirmation number until I ran out of matches.


2. Use a Common Table Expression and have it recursivly loop through to get the max sequential number starting at the generated confirmation number

As a disclaimer, I don't really like using cursors, I prefer to do as much as I can as a batch process. Half the time, I have to look up the syntax to ensure I am properly building up and deallocating the cursor. In short, I took option #2. CTEs are supposed to be easier resource-wise, maybe, possibly. Will map the execution plan at a later time.

In short, the following was the solution chosen. Comments are inline but if you have any questions, comments, concerns, feel free to leave feedback.

-- =============================================
--
Author: Paul Montgomery
-- Create date: 2008.04.23
-- Description: Gets a
time-based confirmation number consisting of number of days
-- Blog:
http://betterlivingthroughcoding.blogspot.com/2008/04/using-cte-instead-of-cursor.html
--
=============================================
CREATE FUNCTION
GetMeAConfirmationNumberForNow()
RETURNS
NVARCHAR(50)
AS
BEGIN
DECLARE @days VARCHAR(5),
@seconds
VARCHAR(5)
SET @days = RIGHT('00000' + CAST(DATEDIFF(d, 0, GetDate()) AS
VARCHAR), 5)
SET @seconds = RIGHT('00000' + CAST(DATEDIFF(s,
CONVERT(varchar(10), GETDATE(), 101), GetDate()) AS VARCHAR), 5)
--Get what
would be this seconds confirmation number
DECLARE @ConfirmationNumber
nvarchar(50)
SET @ConfirmationNumber = @days + @seconds
SET
@ConfirmationNumber = '3955979792'
/*
-- If we were completely sure there
would NEVER be a "future" confirmation
-- number being mistakenly put in the
table, we could just do this.
IF EXISTS (SELECT ConfirmationNumber
FROM
SomeTable
WHERE ConfirmationNumber <> 'SomeBadData' --weed out any
invalid data
AND CAST(ConfirmationNumber AS BIGINT) >
CAST(@ConfirmationNumber AS BIGINT)
BEGIN
SELECT @ConfirmationNumber =
CAST(MAX(CAST(ConfirmationNumber AS BIGINT)) + 1 AS NVARCHAR(50))
FROM
SomeTable
END
-- But since we had some crazy data, I wanted to be sure
that I was going
-- to pull the smallest unused number that was equal to or
greater than the
-- confirmation number genereated via our
algorithm
*/
/*
Select all the confirmation numbers from our table that
are numerically greater than @ConfirmationNumber
*/
DECLARE @TempTable
TABLE(
ConfirmationNumber nvarchar(50))
INSERT INTO
@TempTable(ConfirmationNumber)
SELECT ConfirmationNumber
FROM
SomeTable
WHERE ConfirmationNumber <> 'SomeBadData' --had to remove
some bogus rows
AND CAST(ConfirmationNumber AS BIGINT) >
CAST(@ConfirmationNumber AS BIGINT)
GROUP BY ConfirmationNumber --Yep had
some dupes posted, lukily only in dev/test
--PlaceHolder
DECLARE
@MaxConfirmationNumber nvarchar(50);
--Time for CTE to find my sequential
values
WITH confirmation_numbers (ConfirmationNumber)
AS
(
SELECT
ConfirmationNumber
FROM @TempTable
WHERE CAST(ConfirmationNumber AS
BIGINT) = CAST(@ConfirmationNumber AS BIGINT) + 1
UNION ALL
--This is
where the magic happens
SELECT tt.ConfirmationNumber
FROM @TempTable
tt
INNER JOIN confirmation_numbers
ON CAST(tt.ConfirmationNumber AS
BIGINT) = CAST(confirmation_numbers.ConfirmationNumber AS BIGINT) +1
)
--
We need to increment the max by 1 and get it back to an nvarchar
SELECT
@MaxConfirmationNumber = CAST(MAX(CAST(ConfirmationNumber AS BIGINT)) + 1 AS
NVARCHAR(50))
FROM confirmation_numbers
--Check that we have a this
confirmation or sequential ones higher
IF (@ConfirmationNumber <= @MaxConfirmationNumber) SET @ConfirmationNumber = @MaxConfirmationNumber --SELECT @ConfirmationNumber RETURN @ConfirmationNumber END GO