I see nothing wrong with procedural code if implemeneted properly. The original code has the problem of being too complex and is also limited in scope. I ran it up to 7, beyond which my SQL2000 will not be able to generate a plan. The procedure below is inspired by the original code but is drastically different: it handles large set really fast and dynamic SQL statement is short and recursive (use @debug = 1 to see it).
To use it, you need to store your values in a table with a single column 'x'. This procedure permutes r out of n. Of course you need to make sure r<=n, and be aware that the result set could be substantial (n! / (n-r)! permutations).
It may not be useful, but just for fun.
Create Proc sp_permutate (@n smallint, @t varchar(8), @debug bit = 0)
as
begin
set nocount on
declare @sqlStmt varchar(4000), @delim varchar(2)
declare @i int
declare @j-2 int
if @debug = 1 set @delim = char(10) else set @delim = ''
set @sqlStmt = 'SELECT x' + cast(@n as varchar(2)) + '=X from ' + @t + @delim
set @i = @n -1
while @i > 0
Begin
set @j-2 = @n
set @sqlStmt = 'SELECT x' + cast(@i as varchar(2)) + '=X, T.* from ' + @t +
' join (' + @delim + @sqlStmt + ') T on x<>x' + cast(@j as varchar(2))
while @j-2 > @i
Begin
set @sqlStmt = @sqlStmt + ' and x<>x' + cast(@j as varchar(2))
End
set @sqlStmt = @sqlStmt + @delim
set @i = @i - 1
End
print @sqlStmt
exec (@sqlStmt)
set nocount off
end
go