
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------------------------------------------------------------------------
	

CREATE PROCEDURE [dbo].[p_sys_md_excel_generate_names] 
	@tbl_field_name bit = 1
AS
BEGIN
	SET NOCOUNT ON;
	
	
	declare @tables TABLE(id int identity(1,1), name sysname)
	declare @tbl TABLE(name sysname, const varchar(2000))
	
	insert into @tables(name)
				select 'excel_variables'
	union all	select 'pol_policies' 
	union all	select 'pol_policies_situations'
	union all	select 'pol_variables'
	union all	select 'ris_risks'
	union all	select 'ris_risks_situations'
	union all	select 'ris_variables'
	union all	select 'pr_products'
	union all	select 'pr_products_variables'
	union all	select 'act_cies'
	union all	select 'act_branches'
	union all	select 'act_clients'
	
	
	declare @id int
	declare @max_id int
	declare @min_id int
	declare @name sysname
	declare @const varchar(2000)
	
	select	@min_id = min(id),
			@max_id = max(id)
	from @tables
	
	set @id = @min_id
	
	while @id <= @max_id
	begin
		select	@name = name,
				@const = tbl_l1
		from @tables 
			ìnner join md_tables on tbl_table_name = name 
		where id = @id 
		
		insert into @tbl(name, const) values(upper(@name), 'TABLE ' + @const)
		
		insert into @tbl(name, const)
		select upper(fld_field_name), 'FIELD ' + fld_l1
		from md_fields 
		where fld_table_name = @name
		
		insert into @tbl(name, const) values('', '') 
		
		
		set @id = @id + 1
		
	end
	
	if @tbl_field_name = 1
		select name from @tbl
	else
		select const from @tbl
	
	

END


GO
