La prima community italiana
Generare query dinamiche mediante Stored Procedure | La nostra community
Topic: Pubblico - Composto da 2 Posts di 1 Utente.
| 11 Maggio, 2010 14:34 | #1 | ||
|---|---|---|---|
|
Salve! Ho creato una stored Procedure che genera una query dinamica una volta ricevuti come input i parametri che vengono “prelevati da una form di ricerca”. Questo é il codice : 1DROP PROCEDURE IF EXISTS 'QueryCurs'; 2DELIMITER $$ 3CREATE PROCEDURE QueryCurs (acreditacioCCFMC VARCHAR(2), acreditacioFDR VARCHAR(2), acreditacioUAB VARCHAR(2),alumnesMax VARCHAR(11), 4 alumnesMin VARCHAR(11),anyAcad VARCHAR(10),cliente VARCHAR(60),codiCurs VARCHAR(9), dataFi VARCHAR(10), dataIn VARCHAR(10), demanda VARCHAR(30), 5 departament VARCHAR(30), destinatar VARCHAR (80) , edicio VARCHAR(60), estat VARCHAR(20), hores VARCHAR(11), interlocutor VARCHAR(60), 6 importPreins VARCHAR(50), lloc VARCHAR(2), lloc2 VARCHAR(90),matricula VARCHAR(50), 7 modalitat VARCHAR(20), nomcurs VARCHAR(255), palito VARCHAR(1), responsable VARCHAR(30), 8 sessions VARCHAR(11), tipuscurs VARCHAR(60),tTancat VARCHAR(2), tTancatTitols VARCHAR(2)) 9 10BEGIN 11 DECLARE NotFirst BOOLEAN; 12 DECLARE _statement VARCHAR(2500); 13 DECLARE whereVariable VARCHAR(2300); 14 15 SET _statement = 'SELECT nomcurs, codiCurs, anyAcad, tipusCurs, dataIn, dataFi, estat FROM Curs'; 16 SET NotFirst = FALSE; 17 18 SET _statement = CONCAT(_statement, 'WHERE '); 19 20 IF ( acreditacioCCFMC IS NOT NULL) THEN 21 SET whereVariable = CONCAT(whereVariable, '(acreditacioCCFMC =', acreditacioCCFMC, ') '); 22 SET notFirst = TRUE; 23 END IF; 24 IF (acreditacioFDR IS NOT NULL) THEN 25 IF (notFirst = TRUE) THEN 26 SET whereVariable = CONCAT(whereVariable, 'AND '); 27 END IF; 28 ELSE SET notFirst = TRUE; 29 SET whereVariable = CONCAT(whereVariable, '(acreditacioFDR =', acreditacioFDR, ') '); 30 END IF; 31 IF (acreditacioUAB IS NOT NULL) THEN 32 IF (notFirst = TRUE) THEN 33 SET whereVariable = CONCAT(whereVariable, 'AND '); 34 END IF; 35 ELSE SET notFirst = TRUE; 36 SET whereVariable = CONCAT(whereVariable, '(acreditacioUAB =', acreditacioUAB, ') '); 37 END IF; 38 IF (alumnesMax IS NOT NULL) THEN 39 IF (notFirst = TRUE) THEN 40 SET whereVariable = CONCAT(whereVariable, 'AND '); 41 END IF; 42 ELSE SET notFirst = TRUE; 43 SET whereVariable = CONCAT(whereVariable, '(alumnesMax ', alumnesMax, ') '); 44 END IF; 45 IF (alumnesMin IS NOT NULL) THEN 46 IF (notFirst = TRUE) THEN 47 SET whereVariable = CONCAT(whereVariable, 'AND '); 48 END IF; 49 ELSE SET notFirst = TRUE; 50 SET whereVariable = CONCAT(whereVariable, '(alumnesMin ', alumnesMin, ') '); 51 END IF; 52 IF (anyAcad IS NOT NULL) THEN 53 IF (notFirst = TRUE) THEN 54 SET whereVariable = CONCAT(whereVariable, 'AND '); 55 END IF; 56 ELSE SET notFirst = TRUE; 57 SET whereVariable = CONCAT(whereVariable, '(anyAcad =', anyAcad, ') '); 58 END IF; 59 IF (cliente IS NOT NULL) THEN 60 IF (notFirst = TRUE) THEN 61 SET whereVariable = CONCAT(whereVariable, 'AND '); 62 END IF; 63 ELSE SET notFirst = TRUE; 64 SET whereVariable = CONCAT(whereVariable, '(client =', cliente, ') '); 65 END IF; 66 IF (codiCurs IS NOT NULL) THEN 67 IF (notFirst = TRUE) THEN 68 SET whereVariable = CONCAT(whereVariable, 'AND '); 69 END IF; 70 ELSE SET notFirst = TRUE; 71 SET whereVariable = CONCAT(whereVariable, '(codiCurs =', codiCurs, ') '); 72 END IF; 73 IF (dataFi IS NOT NULL) THEN 74 IF (notFirst = TRUE) THEN 75 SET whereVariable = CONCAT(whereVariable, 'AND '); 76 END IF; 77 ELSE SET notFirst = TRUE; 78 SET whereVariable = CONCAT(whereVariable, '(dataFi ', dataFi, ') '); 79 END IF; 80 IF (dataIn IS NOT NULL) THEN 81 IF (notFirst = TRUE) THEN 82 SET whereVariable = CONCAT(whereVariable, 'AND '); 83 END IF; 84 ELSE SET notFirst = TRUE; 85 SET whereVariable = CONCAT(whereVariable, '(dataIn ', dataIn, ') '); 86 END IF; 87 IF (demanda IS NOT NULL) THEN 88 IF (notFirst = TRUE) THEN 89 SET whereVariable = CONCAT(whereVariable, 'AND '); 90 END IF; 91 ELSE SET notFirst = TRUE; 92 SET whereVariable = CONCAT(whereVariable, '(demanda =', demanda, ') '); 93 END IF; 94 IF (departament IS NOT NULL) THEN 95 IF (notFirst = TRUE) THEN 96 SET whereVariable = CONCAT(whereVariable, 'AND '); 97 END IF; 98 ELSE SET notFirst = TRUE; 99SET whereVariable = CONCAT(whereVariable, '(departament =', departament, ') '); 100 END IF; 101 IF (destinatar IS NOT NULL) THEN 102 IF (notFirst = TRUE) THEN 103 SET whereVariable = CONCAT(whereVariable, 'AND '); 104 END IF; 105 ELSE SET notFirst = TRUE; 106SET whereVariable = CONCAT(whereVariable, '(destinatar =', destinatar, ') '); 107 END IF; 108 IF (edicio IS NOT NULL) THEN 109 IF (notFirst = TRUE) THEN 110 SET whereVariable = CONCAT(whereVariable, 'AND '); 111 END IF; 112 ELSE SET notFirst = TRUE; 113 SET whereVariable = CONCAT(whereVariable, '(edicio =', edicio, ') '); 114 END IF; 115 IF (estat IS NOT NULL) THEN 116 IF (notFirst = TRUE) THEN 117 SET whereVariable = CONCAT(whereVariable, 'AND '); 118 END IF; 119 ELSE SET notFirst = TRUE; 120 SET whereVariable = CONCAT(whereVariable, '(estat =', estat, ') '); 121 END IF; 122 IF (hores IS NOT NULL) THEN 123 IF (notFirst = TRUE) THEN 124 SET whereVariable = CONCAT(whereVariable, 'AND '); 125 END IF; 126 ELSE SET notFirst = TRUE; 127 SET whereVariable = CONCAT(whereVariable, '(hores ', hores, ') '); 128 END IF; 129 IF (interlocutor IS NOT NULL) THEN 130 IF (notFirst = TRUE) THEN 131 SET whereVariable = CONCAT(whereVariable, 'AND '); 132 END IF; 133 ELSE SET notFirst = TRUE; 134SET whereVariable = CONCAT(whereVariable, '(interlocutor =', interlocutor, ') '); 135 END IF; 136 IF (importPreins IS NOT NULL) THEN 137 IF (notFirst = TRUE) THEN 138 SET whereVariable = CONCAT(whereVariable, 'AND '); 139 END IF; 140 ELSE SET notFirst = TRUE; 141SET whereVariable = CONCAT(whereVariable, '(importPreins ', importPreins, ') '); 142 END IF; 143 IF (lloc IS NOT NULL) THEN 144 IF (notFirst = TRUE) THEN 145 SET whereVariable = CONCAT(whereVariable, 'AND '); 146 END IF; 147 ELSE SET notFirst = TRUE; 148 SET whereVariable = CONCAT(whereVariable, '(lloc =', lloc, ') '); 149 END IF; 150 IF (lloc2 IS NOT NULL) THEN 151 IF (notFirst = TRUE) THEN 152 SET whereVariable = CONCAT(whereVariable, 'AND '); 153 END IF; 154 ELSE SET notFirst = TRUE; 155 SET whereVariable = CONCAT(whereVariable, '(lloc2 =', lloc2, ') '); 156 END IF; 157 IF (matricula IS NOT NULL) THEN 158 IF (notFirst = TRUE) THEN 159 SET whereVariable = CONCAT(whereVariable, 'AND '); 160 END IF; 161 ELSE SET notFirst = TRUE; 162 SET whereVariable = CONCAT(whereVariable, '(matricula =', matricula, ') '); 163 END IF; 164 IF (modalitat IS NOT NULL) THEN 165 IF (notFirst = TRUE) THEN 166 SET whereVariable = CONCAT(whereVariable, 'AND '); 167 END IF; 168 ELSE SET notFirst = TRUE; 169 SET whereVariable = CONCAT(whereVariable, '(modalitat =', modalitat, ') '); 170 END IF; 171 IF (nomcurs IS NOT NULL) THEN 172 IF (notFirst = TRUE) THEN 173 SET whereVariable = CONCAT(whereVariable, 'AND '); 174 END IF; 175 ELSE SET notFirst = TRUE; 176 SET whereVariable = CONCAT(whereVariable, '(nomcurs =', nomcurs, ') '); 177 END IF; 178 IF (palito IS NOT NULL) THEN 179 IF (notFirst = TRUE) THEN 180 SET whereVariable = CONCAT(whereVariable, 'AND '); 181 END IF; 182 ELSE SET notFirst = TRUE; 183 SET whereVariable = CONCAT(whereVariable, '(palito =', palito, ') '); 184 END IF; 185 IF (responsable IS NOT NULL) THEN 186 IF (notFirst = TRUE) THEN 187 SET whereVariable = CONCAT(whereVariable, 'AND '); 188 END IF; 189 ELSE SET notFirst = TRUE; 190SET whereVariable = CONCAT(whereVariable, '(responsable =', responsable, ') '); 191 END IF; 192 IF (sessions IS NOT NULL) THEN 193 IF (notFirst = TRUE) THEN 194 SET whereVariable = CONCAT(whereVariable, 'AND '); 195 END IF; 196 ELSE SET notFirst = TRUE; 197 SET whereVariable = CONCAT(whereVariable, '(sessions ', sessions, ') '); 198 END IF; 199 IF (tipuscurs IS NOT NULL) THEN 200 IF (notFirst = TRUE) THEN 201 SET whereVariable = CONCAT(whereVariable, 'AND '); 202 END IF; 203 ELSE SET notFirst = TRUE; 204 SET whereVariable = CONCAT(whereVariable, '(tipuscurs =', tipuscurs, ') '); 205 END IF; 206 IF (tTancat IS NOT NULL) THEN 207 IF (notFirst = TRUE) THEN 208 SET whereVariable = CONCAT(whereVariable, 'AND '); 209 END IF; 210 ELSE SET notFirst = TRUE; 211 SET whereVariable = CONCAT(whereVariable, '(tTancat =', tTancat, ') '); 212 END IF; 213 IF (tTancatTitols IS NOT NULL) THEN 214 IF (notFirst = TRUE) THEN 215 SET whereVariable = CONCAT(whereVariable, 'AND '); 216 END IF; 217 ELSE SET notFirst = TRUE; 218SET whereVariable = CONCAT(whereVariable, '(tTancatTitols =', tTancatTitols, ') '); 219 END IF; 220 SET _statement = CONCAT(_statement,whereVariable); 221 /* un-comment the line below if you want to also view the 222 SQL statement that was just constructed */ 223 #SELECT _statement; 224 SET @statement = _statement; 225 PREPARE dynquery FROM @statement; 226 EXECUTE dynquery; 227 DEALLOCATE PREPARE dynquery; 228END $$ Il problema é che ho creato questa Stored Procedure per poter prendere in input valori NULL e generare dinamicamente una query, il problema é che quando chiamo la Store Procedure ricevo il seguente messaggio d’errore : CALL QueryCurs(NULL, NULL, NULL, NULL, NULL, NULL,NULL,’= 1’,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘NULL’ at line 1 Qualcuno riesce a capire a cosa sia dovuto l’errore? Un saluto! Flavio Ciao Flavio, scusami se mi sono permesso di modificare il tuo post, a dire il vero penso di averti pure inviato molte mail, ti prego di scusarmi. E’ in arrivo il nuovo EDITOR dove questo problema sara’ risolto. Ora che e’ ben impaginata la SP ci possiamo ragionare sopra.
Ultima modifica 11 Luglio, 2010 01:16 di root |
|||
| 13 Maggio, 2010 14:22 | #2 | ||
|---|---|---|---|
|
Ho risolto il problema, copio il codice che magari puó essere utile a qualcuno : 1DROP PROCEDURE IF EXISTS `QueryCurs`; 2 3DELIMITER $$ 4 5CREATE PROCEDURE QueryCurs ( 6acreditacioCCFMC VARCHAR(2), 7acreditacioFDR VARCHAR(2), 8acreditacioUAB VARCHAR(2), 9alumnesMax VARCHAR(11), 10alumnesMin VARCHAR(11), 11anyAcad VARCHAR(10), 12cliente VARCHAR(60), 13codiCurs VARCHAR(9), 14dataFi VARCHAR(10), 15dataIn VARCHAR(10), 16demanda VARCHAR(30), 17departament VARCHAR(30), 18destinatar VARCHAR (80) , 19edicio VARCHAR(60), 20estat VARCHAR(20), 21hores VARCHAR(11), 22interlocutor VARCHAR(60), 23importPreins VARCHAR(50), 24lloc VARCHAR(2), 25lloc2 VARCHAR(90), 26matricula VARCHAR(50), 27modalitat VARCHAR(20), 28nomcurs VARCHAR(255), 29palito VARCHAR(1), 30responsable VARCHAR(30), 31sessions VARCHAR(11), 32tipuscurs VARCHAR(60), 33tTancat VARCHAR(2), 34tTancatTitols VARCHAR(2)) 35 36BEGIN 37 38 DECLARE _statement VARCHAR(500); 39 DECLARE notFirst BOOLEAN DEFAULT FALSE; 40 SET _statement = 'SELECT nomcurs, codiCurs, anyAcad, tipusCurs, dataIn, dataFi, estat FROM CURS '; 41 42 IF (acreditacioCCFMC IS NOT NULL) THEN 43 SET notFirst = TRUE; 44 SET _statement = CONCAT(_statement, 'WHERE '); 45 SET _statement = CONCAT(_statement, '(acreditacioCCFMC =',"'", acreditacioCCFMC ,"'", ') '); 46 END IF; 47 IF acreditacioFDR IS NOT NULL THEN 48 IF (notFirst = TRUE) THEN 49 SET _statement = CONCAT(_statement, 'AND '); 50 ELSE 51 SET _statement = CONCAT(_statement, 'WHERE '); 52 SET notFirst = TRUE; 53 END IF; 54 SET _statement = CONCAT(_statement, '(acreditacioFDR =',"'", acreditacioFDR , "'",') '); 55 END IF; 56 IF acreditacioUAB IS NOT NULL THEN 57 IF (notFirst = TRUE) THEN 58 SET _statement = CONCAT(_statement, 'AND '); 59 ELSE 60 SET _statement = CONCAT(_statement, 'WHERE '); 61 SET notFirst = TRUE; 62 END IF; 63 SET _statement = CONCAT(_statement, '(acreditacioUAB =', "'", acreditacioUAB , "'", ') '); 64 END IF; 65 IF alumnesMax IS NOT NULL THEN 66 IF (notFirst = TRUE) THEN 67 SET _statement = CONCAT(_statement, 'AND '); 68 ELSE 69 SET _statement = CONCAT(_statement, 'WHERE '); 70 SET notFirst = TRUE; 71 END IF; 72 SET _statement = CONCAT(_statement, '(alumnesMax', alumnesMax , ') '); 73 END IF; 74 IF alumnesMin IS NOT NULL THEN 75 IF (notFirst = TRUE) THEN 76 SET _statement = CONCAT(_statement, 'AND '); 77 ELSE 78 SET _statement = CONCAT(_statement, 'WHERE '); 79 SET notFirst = TRUE; 80 END IF; 81 SET _statement = CONCAT(_statement, '(alumnesMin ', alumnesMin , ') '); 82 END IF; 83 IF anyAcad IS NOT NULL THEN 84 IF (notFirst = TRUE) THEN 85 SET _statement = CONCAT(_statement, 'AND '); 86 ELSE 87 SET _statement = CONCAT(_statement, 'WHERE '); 88 SET notFirst = TRUE; 89 END IF; 90 SET _statement = CONCAT(_statement, '(anyAcad = ', "'", anyAcad ,"'", ') '); 91 END IF; 92 IF cliente IS NOT NULL THEN 93 IF (notFirst = TRUE) THEN 94 SET _statement = CONCAT(_statement, 'AND '); 95 ELSE 96 SET _statement = CONCAT(_statement, 'WHERE '); 97 SET notFirst = TRUE; 98 END IF; 99 SET _statement = CONCAT(_statement, '(cliente = ', "'", cliente ,"'", ') '); 100 END IF; 101 IF codiCurs IS NOT NULL THEN 102 IF (notFirst = TRUE) THEN 103 SET _statement = CONCAT(_statement, 'AND '); 104 ELSE 105 SET _statement = CONCAT(_statement, 'WHERE '); 106 SET notFirst = TRUE; 107 END IF; 108 SET _statement = CONCAT(_statement, '(codiCurs ', codiCurs , ') '); 109 END IF; 110 IF dataFi IS NOT NULL THEN 111 IF (notFirst = TRUE) THEN 112 SET _statement = CONCAT(_statement, 'AND '); 113 ELSE 114 SET _statement = CONCAT(_statement, 'WHERE '); 115 SET notFirst = TRUE; 116 END IF; 117 SET _statement = CONCAT(_statement, '(dataFi ', dataFi , ') '); 118 END IF; 119 IF dataIn IS NOT NULL THEN 120 IF (notFirst = TRUE) THEN 121 SET _statement = CONCAT(_statement, 'AND '); 122 ELSE 123 SET _statement = CONCAT(_statement, 'WHERE '); 124 SET notFirst = TRUE; 125 END IF; 126 SET _statement = CONCAT(_statement, '(dataIn ', dataIn , ') '); 127 END IF; 128 IF demanda IS NOT NULL THEN 129 IF (notFirst = TRUE) THEN 130 SET _statement = CONCAT(_statement, 'AND '); 131 ELSE 132 SET _statement = CONCAT(_statement, 'WHERE '); 133 SET notFirst = TRUE; 134 END IF; 135 SET _statement = CONCAT(_statement, '(demanda = ', "'", demanda ,"'", ') '); 136 END IF; 137 IF departament IS NOT NULL THEN 138 IF (notFirst = TRUE) THEN 139 SET _statement = CONCAT(_statement, 'AND '); 140 ELSE 141 SET _statement = CONCAT(_statement, 'WHERE '); 142 SET notFirst = TRUE; 143 END IF; 144 SET _statement = CONCAT(_statement, '(departament = ', "'", departament ,"'", ') '); 145 END IF; 146 IF destinatar IS NOT NULL THEN 147 IF (notFirst = TRUE) THEN 148 SET _statement = CONCAT(_statement, 'AND '); 149 ELSE 150 SET _statement = CONCAT(_statement, 'WHERE '); 151 SET notFirst = TRUE; 152 END IF; 153 SET _statement = CONCAT(_statement, '(destinatar = ', "'", destinatar , "'", ') '); 154 END IF; 155 IF edicio IS NOT NULL THEN 156 IF (notFirst = TRUE) THEN 157 SET _statement = CONCAT(_statement, 'AND '); 158 ELSE 159 SET _statement = CONCAT(_statement, 'WHERE '); 160 SET notFirst = TRUE; 161 END IF; 162 SET _statement = CONCAT(_statement, '(edicio = ', "'", edicio , "'",') '); 163 END IF; 164 IF hores IS NOT NULL THEN 165 IF (notFirst = TRUE) THEN 166 SET _statement = CONCAT(_statement, 'AND '); 167 ELSE 168 SET _statement = CONCAT(_statement, 'WHERE '); 169 SET notFirst = TRUE; 170 END IF; 171 SET _statement = CONCAT(_statement, '(hores ', hores , ') '); 172 END IF; 173 IF interlocutor IS NOT NULL THEN 174 IF (notFirst = TRUE) THEN 175 SET _statement = CONCAT(_statement, 'AND '); 176 ELSE 177 SET _statement = CONCAT(_statement, 'WHERE '); 178 SET notFirst = TRUE; 179 END IF; 180 SET _statement = CONCAT(_statement, '(interlocutor = ',"'", interlocutor , "'",') '); 181 END IF; 182 IF importPreins IS NOT NULL THEN 183 IF (notFirst = TRUE) THEN 184 SET _statement = CONCAT(_statement, 'AND '); 185 ELSE 186 SET _statement = CONCAT(_statement, 'WHERE '); 187 SET notFirst = TRUE; 188 END IF; 189 SET _statement = CONCAT(_statement, '(importPreins ', importPreins , ') '); 190 END IF; 191 IF lloc IS NOT NULL THEN 192 IF (notFirst = TRUE) THEN 193 SET _statement = CONCAT(_statement, 'AND '); 194 ELSE 195 SET _statement = CONCAT(_statement, 'WHERE '); 196 SET notFirst = TRUE; 197 END IF; 198 SET _statement = CONCAT(_statement, '(lloc = ', "'",lloc , "'",') '); 199 END IF; 200 IF lloc2 IS NOT NULL THEN 201 IF (notFirst = TRUE) THEN 202 SET _statement = CONCAT(_statement, 'AND '); 203 ELSE 204 SET _statement = CONCAT(_statement, 'WHERE '); 205 SET notFirst = TRUE; 206 END IF; 207 SET _statement = CONCAT(_statement, '(lloc2 = ', "'",lloc2 , "'",') '); 208 END IF; 209 IF matricula IS NOT NULL THEN 210 IF (notFirst = TRUE) THEN 211 SET _statement = CONCAT(_statement, 'AND '); 212 ELSE 213 SET _statement = CONCAT(_statement, 'WHERE '); 214 SET notFirst = TRUE; 215 END IF; 216 SET _statement = CONCAT(_statement, '(matricula ', matricula , ') '); 217 END IF; 218 IF modalitat IS NOT NULL THEN 219 IF (notFirst = TRUE) THEN 220 SET _statement = CONCAT(_statement, 'AND '); 221 ELSE 222 SET _statement = CONCAT(_statement, 'WHERE '); 223 SET notFirst = TRUE; 224 END IF; 225 SET _statement = CONCAT(_statement, '(modalitat = ', "'",modalitat ,"'", ') '); 226 END IF; 227 IF nomcurs IS NOT NULL THEN 228 IF (notFirst = TRUE) THEN 229 SET _statement = CONCAT(_statement, 'AND '); 230 ELSE 231 SET _statement = CONCAT(_statement, 'WHERE '); 232 SET notFirst = TRUE; 233 END IF; 234 SET _statement = CONCAT(_statement, '(nomcurs = ', "'",nomcurs , "'",') '); 235 END IF; 236 IF palito IS NOT NULL THEN 237 IF (notFirst = TRUE) THEN 238 SET _statement = CONCAT(_statement, 'AND '); 239 ELSE 240 SET _statement = CONCAT(_statement, 'WHERE '); 241 SET notFirst = TRUE; 242 END IF; 243 SET _statement = CONCAT(_statement, '(palito = ', "'",palito , "'",') '); 244 END IF; 245 IF responsable IS NOT NULL THEN 246 IF (notFirst = TRUE) THEN 247 SET _statement = CONCAT(_statement, 'AND '); 248 ELSE 249 SET _statement = CONCAT(_statement, 'WHERE '); 250 SET notFirst = TRUE; 251 END IF; 252 SET _statement = CONCAT(_statement, '(responsable = ', "'",responsable ,"'", ') '); 253 END IF; 254 IF sessions IS NOT NULL THEN 255 IF (notFirst = TRUE) THEN 256 SET _statement = CONCAT(_statement, 'AND '); 257 ELSE 258 SET _statement = CONCAT(_statement, 'WHERE '); 259 SET notFirst = TRUE; 260 END IF; 261 SET _statement = CONCAT(_statement, '(sessions ', sessions , ') '); 262 END IF; 263 IF tipuscurs IS NOT NULL THEN 264 IF (notFirst = TRUE) THEN 265 SET _statement = CONCAT(_statement, 'AND '); 266 ELSE 267 SET _statement = CONCAT(_statement, 'WHERE '); 268 SET notFirst = TRUE; 269 END IF; 270 SET _statement = CONCAT(_statement, '(tipuscurs = ', "'",tipuscurs , "'",') '); 271 END IF; 272 IF tTancat IS NOT NULL THEN 273 IF (notFirst = TRUE) THEN 274 SET _statement = CONCAT(_statement, 'AND '); 275 ELSE 276 SET _statement = CONCAT(_statement, 'WHERE '); 277 SET notFirst = TRUE; 278 END IF; 279 SET _statement = CONCAT(_statement, '(tTancat = ', "'",tTancat , "'",') '); 280 END IF; 281 IF tTancatTitols IS NOT NULL THEN 282 IF (notFirst = TRUE) THEN 283 SET _statement = CONCAT(_statement, 'AND '); 284 ELSE 285 SET _statement = CONCAT(_statement, 'WHERE '); 286 SET notFirst = TRUE; 287 END IF; 288 SET _statement = CONCAT(_statement, '(tTancatTitols = ', "'",tTancatTitols , "'",') '); 289 290 END IF; 291 292 SELECT _statement; 293 294 SET @statement = _statement; 295 PREPARE dynquery FROM @statement; 296 EXECUTE dynquery; 297 DEALLOCATE PREPARE dynquery; 298 299END $$ |
|||
Condividi su:
Loggati o Registrati per replicare