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
|
USE [GPAO]
GO
/****** Objet : StoredProcedure [dbo].[caRealisable] Date de génération du script : 11/25/2014 11:20:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[caRealisable]
-- Add the parameters for the stored procedure here
@jour datetime,
@client int,
@article int,
@filtre_client varchar(50),
@filtre_article varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @rq NVarchar(4000);
DECLARE @ParamDefinition AS NVarchar(2000)
DECLARE @qte decimal(19,6);
DECLARE @chiffre decimal(19,6);
SET @rq = 'SELECT @qte = COALESCE(sum(ccl_qte_cde - ccl_qte_livree),0), @chiffre = COALESCE(sum((ccl_qte_cde - ccl_qte_livree) * ccl_prix),0)
FROM cc, clt, ccl, art
WHERE cc_confirme = 1 AND ccl_delai_confirme = ' + @jour + ' AND cc_id = ccl_cc_id AND cc_client = clt_id AND ccl_article_id = art_id';
IF @client > 0
SET @rq = @rq + ' AND clt_id = ' + @client;
ELSE
SET @rq = @rq + ' AND clt_code LIKE ''%' + @filtre_client + '%''';
IF @article > 0
SET @rq = @rq + ' AND art_id = ' + @article;
ELSE
SET @rq = @rq + ' AND art_reference LIKE ''%' + @filtre_article + '%''';
Set @ParamDefinition =
' @qte decimal(19,6),
@chiffre decimal(19,6),
@jour datetime,
@client int,
@article int,
@filtre_client varchar(50),
@filtre_article varchar(50)'
EXECUTE sp_ExecuteSql @rq,
@ParamDefinition,
@qte,
@chiffre,
@jour,
@client,
@article,
@filtre_client,
@filtre_article
SELECT @rq, @qte, @chiffre;
If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF
Return(0)
ErrorHandler:
Return(@@ERROR)
END |
