
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE procedure [dbo].[p_tt_dyn_risk_drivers]
	@risk_situations_table type_table_id readonly
as


declare @sql nvarchar(max)
declare @ParmDefinition nvarchar(200)

set @sql = '

	set nocount on

	declare @drivers_table table(
		rcpl_risk_situation_id int not null,
		rcpl_order int not null,
		rcpl_habituel bit not null,
		rcpl_MO_CNDH_PRENOM nvarchar(128) null,
		rcpl_MO_CNDH_NOM nvarchar(128) null,
		rcpl_MO_CNDH_DTE_NAISS date null,
		rcpl_MO_CNDH_DTE_PERMIS date null,
		rcpl_MO_CNDH_N_PERMIS nvarchar(128))

	if exists ( select 1 from syscolumns where name = (''rcpl_MO_CNDH_NOM'' ))
	begin

		insert into @drivers_table
		select	rcpl_risk_situation_id, 0, 1, rcpl_MO_CNDH_PRENOM, rcpl_MO_CNDH_NOM, rcpl_MO_CNDH_DTE_NAISS, rcpl_MO_CNDH_DTE_PERMIS, rcpl_MO_CNDH_N_PERMIS
		from	ris_cpl c
				inner join @risk_situations_table t on c.rcpl_risk_situation_id = t.id		
		where	len(isnull(rcpl_MO_CNDH_NOM, '''')) > 0

	end

	if exists ( select 1 from syscolumns where name = (''rcpl_MO_CND1_NOM'' ))
	begin

		insert into @drivers_table
		select	rcpl_risk_situation_id, 1, 0, rcpl_MO_CND1_PRENOM, rcpl_MO_CND1_NOM, rcpl_MO_CND1_DTE_NAISS, rcpl_MO_CND1_DTE_PERMIS, rcpl_MO_CND1_N_PERMIS
		from	ris_cpl c
				inner join @risk_situations_table t on c.rcpl_risk_situation_id = t.id		
		where	len(isnull(rcpl_MO_CND1_NOM, '''')) > 0

	end

	if exists ( select 1 from syscolumns where name = (''rcpl_MO_CND2_NOM'' ))
	begin

		insert into @drivers_table
		select	rcpl_risk_situation_id, 2, 0, rcpl_MO_CND2_PRENOM, rcpl_MO_CND2_NOM, rcpl_MO_CND2_DTE_NAISS, rcpl_MO_CND2_DTE_PERMIS, rcpl_MO_CND2_N_PERMIS
		from	ris_cpl c
				inner join @risk_situations_table t on c.rcpl_risk_situation_id = t.id		
		where	len(isnull(rcpl_MO_CND2_NOM, '''')) > 0

	end

	if exists ( select 1 from syscolumns where name = (''rcpl_MO_CND3_NOM'' ))
	begin

		insert into @drivers_table
		select	rcpl_risk_situation_id, 3, 0, rcpl_MO_CND3_PRENOM, rcpl_MO_CND3_NOM, rcpl_MO_CND3_DTE_NAISS, rcpl_MO_CND3_DTE_PERMIS, rcpl_MO_CND3_N_PERMIS
		from	ris_cpl c
				inner join @risk_situations_table t on c.rcpl_risk_situation_id = t.id		
		where	len(isnull(rcpl_MO_CND3_NOM, '''')) > 0

	end

	if exists ( select 1 from syscolumns where name = (''rcpl_MO_CND4_NOM'' ))
	begin

		insert into @drivers_table
		select	rcpl_risk_situation_id, 4, 0, rcpl_MO_CND4_PRENOM, rcpl_MO_CND4_NOM, rcpl_MO_CND4_DTE_NAISS, rcpl_MO_CND4_DTE_PERMIS, rcpl_MO_CND4_N_PERMIS
		from	ris_cpl c
				inner join @risk_situations_table t on c.rcpl_risk_situation_id = t.id		
		where	len(isnull(rcpl_MO_CND4_NOM, '''')) > 0

	end

	if exists ( select 1 from syscolumns where name = (''rcpl_MO_CND5_NOM'' ))
	begin

		insert into @drivers_table
		select	rcpl_risk_situation_id, 5, 0, rcpl_MO_CND5_PRENOM, rcpl_MO_CND5_NOM, rcpl_MO_CND5_DTE_NAISS, rcpl_MO_CND5_DTE_PERMIS, rcpl_MO_CND5_N_PERMIS
		from	ris_cpl c
				inner join @risk_situations_table t on c.rcpl_risk_situation_id = t.id		
		where	len(isnull(rcpl_MO_CND5_NOM, '''')) > 0

	end



	select * from @drivers_table'

	SET @ParmDefinition = N'@risk_situations_table type_table_id readonly';

	EXECUTE sp_executesql @sql, @ParmDefinition,
                      @risk_situations_table = @risk_situations_table

/*
	declare @risk_situations_table type_table_id

	insert into @risk_situations_table
	select top 10 rcpl_risk_situation_id from ris_cpl where isnull(rcpl_MO_CNDH_NOM,'') <> ''
	 
	exec [p_tt_dyn_risk_drivers] @risk_situations_table 
*/


	

GO
