· 9 years ago · Jan 30, 2017, 10:00 PM
1DROP PROCEDURE `update_article_subject`;
2DELIMITER //
3CREATE PROCEDURE `update_article_subject` (IN tn VARCHAR(15), IN created VARCHAR(20), IN new_subject VARCHAR(255), IN optional_old_subject VARCHAR(255), IN test BIT)
4BEGIN
5 DECLARE run_update BIT;
6 DECLARE updated_subject VARCHAR(255);
7 DECLARE affected_count INT;
8 DECLARE successful BIT;
9
10 DROP TEMPORARY TABLE IF EXISTS temp_affected_rows;
11 CREATE TEMPORARY TABLE IF NOT EXISTS temp_affected_rows AS
12 (
13 SELECT t.tn, a.a_subject, DATE_FORMAT(a.create_time, '%m/%d/%Y %H:%i') as 'create_time'
14 FROM article a
15 INNER JOIN ticket t ON t.id = a.ticket_id
16 WHERE t.tn = tn
17 AND DATE_FORMAT(a.create_time, '%m/%d/%Y %H:%i') = created
18 AND (a.a_subject = optional_old_subject or optional_old_subject = '')
19 );
20
21 SELECT COUNT(*)
22 INTO affected_count
23 FROM temp_affected_rows;
24
25 SELECT *
26 FROM temp_affected_rows;
27
28 IF (test = 0 AND affected_count = 1) THEN
29 SET run_update = 1;
30 END IF;
31
32 IF run_update = 1 THEN
33 UPDATE
34 article a
35 INNER JOIN ticket t ON t.id = a.ticket_id
36 SET a.a_subject = new_subject
37 WHERE t.tn = tn
38 AND DATE_FORMAT(a.create_time, '%m/%d/%Y %H:%i') = created
39 AND (a.a_subject = optional_old_subject or optional_old_subject = '');
40
41 SELECT a.a_subject
42 INTO updated_subject
43 FROM article a
44 INNER JOIN ticket t on t.id = a.ticket_id
45 WHERE t.tn = tn
46 AND DATE_FORMAT(a.create_time, '%m/%d/%Y %H:%i') = created
47 AND (a.a_subject = new_subject);
48 END IF;
49
50 IF updated_subject = new_subject THEN
51 SET successful = 1;
52 ELSE
53 SET successful = 0;
54 END IF;
55
56 SELECT affected_count, successful, updated_subject;
57
58 END //
59DELIMITER ;
60
61/*
62Params
63 * @tn: Ticket number
64 * @created: Article create date
65 * @new_subject: New article subject
66 * @optional_old_subject: Old article subject (optional)
67 * @test: Set to 1 for test
68
69call update_article_subject(@tn := '201702251031501', @created := '01/25/2017 11:15', @new_subject := 'MyTicket', @optional_old_subject := 'MyTicket_123', @test := 0);
70call update_article_subject(@tn := '201610151064565', @created := '11/28/2016 12:01', @new_subject := 'Hello', @optional_old_subject := '', @test := 1);
71*/