Bonjour,

Je souhaite de faire ceci avec un SQL stored procedure

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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

Create procedure MktCacheGetXXXX
(
    @Provider       varchar(10),
    @AsOfDate     datetime,
    @Instrument   varchar(20),
    @Type           varchar(20),
    @ListNames    varchar(200)
)
As
Begin    
    Declare @TableName  varchar(20)
    Set @TableName = Upper(@Type)

        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From @TableName
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE


End

Au lieu de faire comme ceci:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143


    If Upper(@Type) = 'COCO'
    Begin
        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From COCO
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE
    End
    Else If Upper(@Type) = 'COEQ'
    Begin
        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From COEQ
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE
    End
    Else If Upper(@Type) = 'COFX'
    Begin
        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From COFX
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE
    End
    Else If Upper(@Type) = 'COIR'
    Begin
        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From COIR
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE
    End
    Else If Upper(@Type) = 'EQEQ'
    Begin
        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From EQEQ
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE
    End
    Else If Upper(@Type) = 'EQFX'
    Begin
        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From EQFX
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE
    End
    Else If Upper(@Type) = 'EQIR'
    Begin
        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From EQIR
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE
    End
    Else If Upper(@Type) = 'FXFX'
    Begin
        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From FXFX
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE
    End
    Else If Upper(@Type) = 'FXIR'
    Begin
        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From FXIR
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE
    End
    Else If Upper(@Type) = 'IRIR'
    Begin
        Select
            InstrumentOne   = M_TYPE,
            InstrumentTwo   = M_NAME,
            Object          = M_OBJECT
        From IRIR
        Where M_INSTRUMENT in (@Instrument)
            and M_PROVIDER in (@Provider)
            and M_DATE in (@AsOfDate)
            and M_TYPE in (@ListNames)
            and M_NAME in (@ListNames)
        Order by M_PROVIDER, M_INSTRUMENT, M_DATE, M_TYPE
    End
Les tables ont les mêmes colonnes (même nom) mais leur contenu est différent.

Merci