1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
| /*déclaration d'une table temporaire*/
create table tempo(
sContact_vo_email nvarchar(255),tsLastModif datetime,
iCounty_vo_1 varchar,iCounty_vo_2 varchar/*,iCounty_vo_3 varchar,iCounty_vo_4 varchar,iCounty_vo_5 varchar,iCounty_vo_6 varchar,iCounty_vo_7 varchar,iCounty_vo_8 varchar,iCounty_vo_9 varchar*/,
iMileage_vo_1 varchar,iMileage_vo_2 varchar/*,iMileage_vo_3 varchar,iMileage_vo_4 varchar,iMileage_vo_5 varchar,iMileage_vo_6 varchar,iMileage_vo_7 varchar,iMileage_vo_8 varchar,iMileage_vo_9 varchar*/,
tsCreation datetime,
iPrice_vo_1 int,iPrice_vo_2 int/*,iPrice_vo_3 int,iPrice_vo_4 int,iPrice_vo_5 int,iPrice_vo_6 int,iPrice_vo_7 int,iPrice_vo_8 int,iPrice_vo_9 int*/,
iModel_year_vo_1 int,iModel_year_vo_2 int/*,iModel_year_vo_3 int,iModel_year_vo_4 int,iModel_year_vo_5 int,iModel_year_vo_6 int,iModel_year_vo_7 int,iModel_year_vo_8 int,iModel_year_vo_9 int*/,
sBrand_vo_1 nvarchar(255), sBrand_vo_2 nvarchar(255)/*,sBrand_vo_3 nvarchar(255),sBrand_vo_4 nvarchar(255),sBrand_vo_5 nvarchar(255),sBrand_vo_6 nvarchar(255),sBrand_vo_7 nvarchar(255),sBrand_vo_8 nvarchar(255),sBrand_vo_9 nvarchar(255)*/,
sDetail_url_vo_1 nvarchar(255),sDetail_url_vo_2 nvarchar(255)/*,sDetail_url_vo_3 nvarchar(255),sDetail_url_vo_4 nvarchar(255),sDetail_url_vo_5 nvarchar(255),sDetail_url_vo_6 nvarchar(255),sDetail_url_vo_7 nvarchar(255),sDetail_url_vo_8 nvarchar(255),sDetail_url_vo_9 nvarchar(255)*/,
sModel_name_vo_1 nvarchar(255),sModel_name_vo_2 nvarchar(255)/*,sModel_name_vo_3 nvarchar(255),sModel_name_vo_4 nvarchar(255),sModel_name_vo_5 nvarchar(255),sModel_name_vo_6 nvarchar(255),sModel_name_vo_7 nvarchar(255),sModel_name_vo_8 nvarchar(255),sModel_name_vo_9 nvarchar(255)*/,
sThumbnail_url_vo_1 nvarchar(255),sThumbnail_url_vo_2 nvarchar(255)/*,sThumbnail_url_vo_3 nvarchar(255),sThumbnail_url_vo_4 nvarchar(255),sThumbnail_url_vo_5 nvarchar(255),sThumbnail_url_vo_6 nvarchar(255),sThumbnail_url_vo_7 nvarchar(255),sThumbnail_url_vo_8 nvarchar(255),sThumbnail_url_vo_9 nvarchar(255)*/,
sUnsubscribe_url_vo_1 datetime,sUnsubscribe_url_vo_2 datetime/*,sUnsubscribe_url_vo_3 datetime,sUnsubscribe_url_vo_4 datetime,sUnsubscribe_url_vo_5 datetime,sUnsubscribe_url_vo_6 datetime,sUnsubscribe_url_vo_7 datetime,sUnsubscribe_url_vo_8 datetime,sUnsubscribe_url_vo_9 datetime*/,
tsDate_vo datetime,
sAlert_name_vo_1 nvarchar(255),sAlert_name_vo_2 nvarchar(255),/*pour tester si on doit le faire sortir de la boucle si tout marche , sAlert_name_vo_2 nvarchar(255),sAlert_name_vo_3 nvarchar(255),sAlert_name_vo_4 nvarchar(255),sAlert_name_vo_5 nvarchar(255),sAlert_name_vo_6 nvarchar(255),sAlert_name_vo_7 nvarchar(255),sAlert_name_vo_8 nvarchar(255),sAlert_name_vo_9 nvarchar(255)*/
)
/*
insert into tempo
select TA.sContact_vo_email, TA.tsLastModif, TA.tsCreation,
TB.iPrice_vo, TB.iModel_year_vo, TB.sBrand_vo, TB.sDetail_url_vo, TB.sModel_name_vo,
TB.sThumbnail_url_vo, TB.sUnsubscribe_url_vo, TB.tsDate_vo, TB.sAlert_name_vo
From neolane.BmwContact_vo2 TA
LEFT OUTER JOIN neolane.BmwAlert_vo2 TB on TA.sContact_vo_email=TB.sContact_email_vo
*/
/*déclarartion des variables*/
declare @iCounty_vo varchar(255),
@iMileage_vo varchar(255),
@iModel_year_vo varchar(255),
@iPrice_vo varchar(255)
declare @sBrand_vo varchar(255),
@sDetail_url_vo varchar(255),
@sModel_name_vo varchar(255),
@sThumbnail_url_vo varchar(255),
@sUnsubscribe_url_vo varchar(255),
@sAlert_name_vo varchar(50)
declare @pivot varchar(200),
@sum varchar(200),
@sql nvarchar(1000),
@col varchar(255),
@sql_part1 varchar(4000),
@sql_part2 varchar(4000),
@compteur int
select @pivot='', @sum=''
/*selectionner les mails des personnes */
declare c cursor
for select distinct sContact_vo_email
from neolane.BmwContact_vo2
order by sContact_vo_email
open c
fetch c into @col
while @@FETCH_STATUS = 0
begin
/*deux partie de chaines la première pour la commande et la deuxième pour les serie de données*/
set @sql_part1 = 'insert tempo(sContact_vo_email '
set @sql_part2 = ' Values(''' + @col + ''','''
/*récupération des alertes par email*/
declare cAlert cursor for select iCounty_vo,iMileage_vo,iModel_year_vo,iPrice_vo,
sBrand_vo,sDetail_url_vo,sModel_name_vo,sThumbnail_url_vo,sUnsubscribe_url_vo,sAlert_name_vo
from neolane.BmwAlert_vo2
where sContact_email_vo = @col
/* ajout des infos de chaque alerte dans la table temporaire*/
open cAlert
fetch cAlert into @iCounty_vo,@iMileage_vo,@iModel_year_vo,@iPrice_vo,
@sBrand_vo,@sDetail_url_vo,@sModel_name_vo,@sThumbnail_url_vo,@sUnsubscribe_url_vo,@sAlert_name_vo
set @compteur = 1
while (@@FETCH_STATUS = 0 and @compteur <3)
begin
set @sql_part1 = @sql_part1 + ',iCounty_vo_' + convert(varchar,@compteur)
+ ',iMileage_vo_' + convert(varchar,@compteur)
+ ',iModel_year_vo_' + convert(varchar,@compteur)
+ ',iPrice_vo_' + convert(varchar,@compteur)
+ ',sBrand_vo_' + convert(varchar,@compteur)
+ ',sDetail_url_vo_' + convert(varchar,@compteur)
+',sModel_name_vo_' + convert(varchar,@compteur)
+',sThumbnail_url_vo_' + convert(varchar,@compteur)
+',sUnsubscribe_url_vo_'+ convert(varchar,@compteur)
+',sAlert_name_vo_' + convert(varchar,@compteur)
set @sql_part2 = @sql_part2 + isnull(@iCounty_vo,'') + ''',''' + isnull(@iMileage_vo,'') + ''',''' + isnull(@iModel_year_vo,'') + ''',''' + isnull(@iPrice_vo,'') + ''','''+ isnull(@sBrand_vo,'') + ''',''' + isnull(@sDetail_url_vo,'') + ''',''' + isnull(@sModel_name_vo,'') + ''',''' + isnull(@sThumbnail_url_vo,'') + ''',''' + isnull(@sUnsubscribe_url_vo,'') + ''',''' + isnull(@sAlert_name_vo,'')
fetch cAlert into @iCounty_vo,@iMileage_vo,@iModel_year_vo,@iPrice_vo,
@sBrand_vo,@sDetail_url_vo,@sModel_name_vo,@sThumbnail_url_vo,@sUnsubscribe_url_vo,@sAlert_name_vo
set @compteur = @compteur + 1
end
close cAlert
deallocate cAlert
/* avant exécution fermer les parenthèses*/
set @sql_part1 = @sql_part1 + ')'
set @sql_part2= @sql_part2 +',sAlert_name_vo_' + convert(varchar,@compteur)+ ''')'
exec (@sql_part1 + ' ' + @sql_part2)
fetch c into @col
end
close c
deallocate c
select * from tempo
drop table tempo |
Partager