Generating combinations 'm elements from n-element set'

/******************************************************************************
*
* Author Rafal Skotak
* Purpose This procedure generates a dataset with combinations of
* elements_to_select taken from number_of_values element set
* Date 2008-01-2
*
*******************************************************************************/

if exists(select * from sys.objects where object_id = object_id('dbo.proc_generate_combinations') and type = 'P')    
drop procedure dbo.proc_generate_combinations
go

create procedure dbo.proc_generate_combinations
@number_of_values integer,
@elements_to_select integer
as
begin
--------------------------------------------------------------
-- check parameters values

if @number_of_values is null
begin
raiserror('Number of values is null', 16, 1)
return
end

if @elements_to_select is null
begin
raiserror('Elements to select is null', 16, 1)
return
end

if @number_of_values < 1 or @number_of_values > 18
begin
raiserror('Invalid value of number_of_values', 16, 1)
return
end

if @elements_to_select > @number_of_values or @elements_to_select < 1
begin
raiserror('Invalid value of elements_to_select', 16, 1)
return
end

--------------------------------------------------------------------------------------
-- prepare parts of the query

declare @n_unions nvarchar(max)
declare @n_select_list nvarchar(max)
declare @n_glued_value nvarchar(max)
declare @n_glued_ascii_value nvarchar(max)
declare @n_joins nvarchar(max)
declare @n_counter nvarchar(16)

declare @values_counter int

set @values_counter = 0

set @n_unions = N''
set @n_select_list = N''
set @n_glued_value = N''
set @n_glued_ascii_value = N''
set @n_joins = N''

while @values_counter < @number_of_values
begin
set @n_counter = cast(@values_counter as nvarchar(16))
set @n_unions = @n_unions + N' select ' + @n_counter + N' as val union '

if @values_counter < @elements_to_select
begin
set @n_select_list = @n_select_list + char(13) + N' comb' + @n_counter + N'.val, '

set @n_glued_value = @n_glued_value + char(13) + N' cast(comb' + @n_counter + N'.val as varchar(64)) + '

set @n_glued_ascii_value = @n_glued_ascii_value + char(13) + N' char(comb' + @n_counter + N'.val + ascii(''A'')) + '
end

if @values_counter < @elements_to_select - 1
begin
set @n_joins = @n_joins + N' inner join ' + char(13) + N' comb as comb' + cast((@values_counter + 1) as nvarchar(16)) 
+ N' on ' + char(13) + ' comb' + @n_counter + '.val < comb' + cast((@values_counter + 1) as nvarchar(16)) + '.val '
end

set @values_counter = @values_counter + 1
end

set @n_unions = substring(@n_unions, 1, len(@n_unions) - 6)

set @n_glued_value = substring(@n_glued_value, 1, len(@n_glued_value) - 2)

set @n_glued_ascii_value = substring(@n_glued_ascii_value, 1, len(@n_glued_ascii_value) - 2)

------------------------------------------------------------------------------------
-- assemble the main query

declare @n_cmd nvarchar(max)

set @n_cmd = N'with comb as (' + char(13) + @n_unions + char(13) + N')' + char(13) + N'select ' + @n_select_list 

set @n_cmd = @n_cmd + @n_glued_value + N'as res_glued, ' + char(13) + @n_glued_ascii_value + N'res_text ' + char(13)

set @n_cmd = @n_cmd + 'from ' + char(13) + ' comb as comb0 ' + @n_joins + char(13) + N'order by res_text'

print @n_cmd

exec sp_executesql @n_cmd
end
go

-- example:

exec dbo.proc_generate_combinations 16, 8