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.


No comments:

Post a Comment