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