Ok, please see how the same thing can be done wihtou using any loop:

CREATE PROCEDURE [dbo].[PS_DIM_TEMPS] /*(@Date_debut varchar(10), @Date_fin varchar(10))*/

AS

truncate table DWProd.dbo.DIM_TEMPS

declare @D_DEBUT datetime

declare @D_FIN datetime

BEGIN

/*set @D_DEBUT = @Date_debut

set @D_FIN = @Date_fin*/

SET @D_DEBUT = cast('2009-10-01' as datetime)

SET @D_FIN = cast('2013-09-30' as datetime)

-- the following CTE will produce required rtange of dates!

-- please note: it's based on your exiosting logic which excludes D_FIN from range!

;WITH E1(N) AS ( SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V(N)) -- 10E+1 or 10 rows

,E2(N) AS (SELECT 1 FROM E1 a, E1 b) --10E+2 or 100 rows

,E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max (covers around 27 years)

,cteDays(D) AS (SELECT TOP (DATEDIFF(d, @D_DEBUT, @D_FIN)) DATEADD(d,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))-1, @D_DEBUT) FROM E4)

INSERT INTO DIM_TEMPS(DATEID,ANNEE,TRIMESTRE, MOIS, LIB_MOIS, SEMAINE, JOUR,JOUR_SEMAINE

,QUINZAINE,CAMPAGNE,ANNEE_CAMP,ORDRE_MOIS)

SELECT D AS Date_Complete

,DATENAME(YEAR, D) AS Annee

,DATEPART(QUARTER, D) AS trimestre

,DATEPART(MONTH, D) AS mois

,DATENAME(MONTH, D) AS mois_complete

,DATEPART(WEEK, D) AS semaine_annee

,DATEPART(DAY, D) AS jour

,DATEPART(WEEKDAY, D) AS jour_semaine

,CASE WHEN DATEPART(DAY, D) < 16 THEN 1 ELSE 2 END AS quinzaine

,CASE WHEN DATEPART(MONTH, D) >9

THEN RIGHT(CAST(YEAR(D) AS CHAR(4)),2) + '/'

+ RIGHT(CAST(YEAR(D) + 1 AS CHAR(4)),2)

ELSE RIGHT(CAST(YEAR(D) - 1 AS CHAR(4)),2) + '/'

+ RIGHT(CAST(YEAR(D) AS CHAR(4)),2)

END AS campagne

,CASE WHEN DATEPART(MONTH, D) > 9

THEN YEAR(D) + 1

ELSE YEAR(D)

END AS annee2

,DATEPART(MONTH, D) + 3 - ((DATEPART(MONTH, D)+2)/12) * 12 AS ordre_mois

FROM cteDays

Update DIM_TEMPS set DECADE=case When day(DATEID) between 1 and 10 then convert(varchar(2),month(DATEID))+'-D1'

When day(DATEID) between 11 and 20 then convert(varchar(2),month(DATEID))+'-D2'

When day(DATEID) between 21 and 31 then convert(varchar(2),month(DATEID))+'-D3' END

END

GO

The above code will be way faster then loop based one.

Now about datatypes (possible conversion errors). You did not supplied DDL of DIM_TEMPS table, so I could only guess required datatype where explicit conversion was maid.

Now please check with your language requirements for month names (mois_complete)

If they need to be in French, you will need to make sure that your database default language is French, or use "SET LANGUAGE French" before INSERT as I did show to you before.