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
| CREATE PROCEDURE Select_scans_product
@product_barcode_product nvarchar(50) = null,
@product_variety_name_product nvarchar(100) = null,
@product_range_name_product nvarchar(100) = null,
@product_comment_product nvarchar(500) = null,
@product_is_visible_product bit = null,
@creation_origin_product nvarchar(50) = null,
@is_non_alimentaire_product bit = null,
@view_1_quantite integer = null,
@view_1_quantite_order nvarchar = null,
@product_barcode_product_order nvarchar = null,
@product_calories_product real = null,
@product_glucides_product real = null,
@product_lipides_product real = null,
@product_protides_product real = null,
@product_ingredient_product text = null,
@perimetre_user nvarchar = null,
@creation_date_product_order datetime = null,
@quantite_scans_order nvarchar = null
AS
DECLARE @var_perimetre_user nvarchar;
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @var_perimetre_user = @perimetre_user
SELECT
product.pk_product,
product.barcode_product,
product.variety_name_product,
product.range_name_product,
product.creation_date_product,
product.comment_product,
product.is_visible_product,
product.image_product,
product.ingredients_product,
product.calories_product,
product.glucides_product,
product.lipides_product,
product.protides_product,
product.is_non_alimentaire_product,
Family_NameFr,
Family_NameUs,
SubFam_NameFr,
SubFam_NameUs,
creation_origin_product,
View_1.quantite,
View_quantite_scans.quantite_scans
FROM
product LEFT OUTER JOIN
(SELECT
barcode_product,
COUNT(*) AS quantite
FROM
dbo.product AS product_1
WHERE
COALESCE (is_deleted_product, 0) = 0
GROUP BY barcode_product) as View_1 ON product.barcode_product = View_1.barcode_product LEFT OUTER JOIN
View_quantite_scans ON product.barcode_product = View_quantite_scans.barcode_product LEFT OUTER JOIN
XTC_tSubFamily ON product.fk_sub_family_product = XTC_tSubFamily.SubFamilyId LEFT OUTER JOIN
XTC_tFamily ON XTC_tSubFamily.SubFam_FamilyId = XTC_tFamily.FamilyId
WHERE
COALESCE(is_deleted_product,0) = 0
AND (@product_barcode_product IS NULL OR product.barcode_product like @product_barcode_product)
AND (@product_variety_name_product IS NULL OR (LOWER(product.variety_name_product) LIKE @product_variety_name_product)
OR (@product_range_name_product IS NULL OR LOWER(product.range_name_product) LIKE @product_range_name_product)
OR (@product_comment_product IS NULL OR LOWER(product.comment_product) LIKE @product_comment_product))
AND (@product_is_visible_product IS NULL OR product.is_visible_product = @product_is_visible_product)
AND (@creation_origin_product IS NULL OR creation_origin_product = @creation_origin_product)
AND (@is_non_alimentaire_product IS NULL OR COALESCE(is_non_alimentaire_product,0) = @is_non_alimentaire_product)
AND (@product_calories_product IS NULL OR product.calories_product = @product_calories_product)
AND (@product_glucides_product IS NULL OR product.glucides_product = @product_glucides_product)
AND (@product_lipides_product IS NULL OR product.lipides_product = @product_lipides_product)
AND (@product_protides_product IS NULL OR product.protides_product = @product_protides_product)
AND (@product_ingredient_product IS NULL OR product.ingredients_product LIKE @product_ingredient_product)
AND (@var_perimetre_user IS NULL OR @var_perimetre_user)
ORDER BY CASE
WHEN (@view_1_quantite_order IS NOT NULL) THEN View_1.quantite
WHEN (@product_barcode_product_order IS NOT NULL) THEN product.barcode_product
WHEN (@creation_date_product_order IS NOT NULL) THEN creation_date_product
WHEN (@quantite_scans_order IS NOT NULL) THEN View_quantite_scans.quantite_scans
END DESC
END
GO |
parametre contenant clause where
Répondre avec citation
Partager