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
| ALTER procedure [dbo].[sp_GetOrderDataToExport]
@ExtendDatabase varchar(50),
@StartDate datetime,
@EndDate datetime
as
begin
-- Masque les messages SQL
set nocount on;
-- Déclaration des variables pour la requête dynamique
declare @queryText nvarchar(max);
-- Initialisation du texte de la requête
set @queryText = N'
select
og.order_number as ''Numéro Commande'',
og.order_status as ''Statut Commande'',
og.order_created_date as ''Date Commande'',
usr.usr_ll_email as ''Mail Client'',
usr.usr_ll_lastname as ''Nom Client'',
usr.usr_ll_firstname as ''Prénom Client'',
li.product_sku as ''Ref Fournisseur'',
li.product_name as ''Titre Produit'',
li.quantity as Quantité,
li.used_price as ''Prix de Vente'',
0 as Remise,
og.shipping_total as ''Montant Expedition'',
''CB'' as ''Type Paiement''
from
OrderGroup og (nolock)
inner join
OrderFormLineItems li (nolock) on og.ordergroup_id = li.ordergroup_id
inner join
[%ExtendDatabase%].[dbo].[T_USER_USR] usr (nolock) on og.user_id = usr.usr_oi_id
where
og.order_created_date Between @StartDate AND @EndDate
and
li.sal_oi_id = ''65bf554d-a4f4-4833-8364-467b2ba09d5c'';
-- Remplacement du nom de catalogue et déclaration des paramètres
set @queryText = replace(@queryText, '%ExtendDatabase%', @ExtendDatabase)
-- Exécute la requête SQL construite
execute sp_executesql @queryText;
end |
Partager