Friday, May 11, 2012
TSQL 101 Creating a set of n unique strings
I did this as an exercise in recursive TSQL functions.
with Stuff(one, i) AS (
SELECT CONVERT(NVARCHAR(20), 'one'), 1
UNION ALL
SELECT CONVERT(NVARCHAR(20), 'one' + CONVERT(nvarchar, i)) as one, i + 1
FROM Stuff
WHERE i < 100
)
select one from Stuff
Which returns:
one
one1
one2
...
one99
At first, I kept getting this error:
Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "one" of recursive query "Stuff".
So I changed:
SELECT 'one', 1
To:
SELECT CONVERT(NVARCHAR(20), 'one'), 1
in order to match the recursive part's exact type and it worked.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment