MySQL procedure to accept user input and to show same as result-Not working

Question

I have been struggling in writing MySQL stored procedure that accepts user input and shows the same as result. Here is my sample code... In below code I have declared local variables and displaying that back to user as first step.

---

DELIMITER $$
CREATE PROCEDURE USER_INPUT() 
BEGIN
    declare TITLE VARCHAR(20);
    declare SEVERITY INT;
    declare OPENDATE DATETIME;
    declare CLOSEDATE DATETIME;    
    SET TITLE = 'ABC';
    SET SEVERITY = 1;
    SET OPENDATE = ADDDATE(current_timestamp, -31);
    SET CLOSEDATE =  current_timestamp;
    SELECT 'You Entered '|| TITLE || ' ' || SEVERITY || ' ' || OPENDATE || ' ' || CLOSEDATE;
    SELECT 'You Entered '|| TITLE;
    INSERT INTO BUGS(TITLE, SEVERITY, OPENDATE, CLOSEDATE) VALUES(TITLE, SEVERITY, OPENDATE, CLOSEDATE);
END $$
DELIMITER ;

Invoking as below...
DELIMITER $$
 BEGIN
  CALL USER_INPUT();
 END;
DELIMITER ;

create table `bugs` (
    ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    TITLE VARCHAR(20) NOT NULL,
    SEVERITY INT NOT NULL check(SEVERITY>0 && SEVIRITY<5),
    OPENDATE DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CLOSEDATE DATETIME default NULL
 )ENGINE=MyISAM DEFAULT CHARSET=latin1;

---

Observation...

1.The output of stored procedure run is not as expected. Output screen shot attached.

2. I tried to modify the procedure so that it should take user input... CREATE PROCEDURE USER_INPUT(@TITLE VARCHAR(20), @SEVERITY INT, @OPENDATE DATETIME, @CLOSEDATE DATETIME)

The above is very first line itself results in compilation error. i.e. i am unsure about syntax.

3. Though the output is not as expected surprisingly the insert query has done work correctly(verified that the row gets inserted has correct values)

Please suggest changes in the procedure code so that it accepts user input(instead of setting the values inside the procedure) and shows the result back to the user. Thanks.

Answer 1

The select statement is creating a bizarre escape sequence and it has no intention of providing your output as you wish. Partly because the separations of what you may be trying to do are messed up. Such as, if you are trying to create a header with text or actually output your variables. Look into concat() maybe depending on your needs.

Pulling it back up to 50K feet here. Don't get too bogged down with even doing that. Stored Procs are not mean for User Input and Report Writing primarily (Read: Please don't use them for that). So perhaps you are trying to use them wrong!

Next, the use of User Variables (ones with an @) in calls to the stored proc as parameters is forbidden as you were showing in the text for what you called #2.

Also, you don't need to block off your test call so complicated. Remember that the delimiter is only useful for blocking off your call to create the stored proc. You don't need to code like that for your test call. It would merely be:

CALL USER_INPUT();

So, that would be 5 lines of code reduced to 1.

Try the following

DROP PROCEDURE IF EXISTS USER_INPUT;
DELIMITER $$
CREATE PROCEDURE USER_INPUT
(   TITLE VARCHAR(20),
    SEVERITY INT,
    SomeDateNotUsed DATETIME -- not used
) 
BEGIN
    DECLARE OPENDATE DATETIME;
    DECLARE CLOSEDATE DATETIME;

    SET TITLE = 'ABC';
    SET SEVERITY = 1;
    SET OPENDATE = ADDDATE(current_timestamp, -31);
    SET CLOSEDATE =  current_timestamp;
    SET @str=CONCAT(TITLE,'||',SEVERITY,'||',OPENDATE,'||',CLOSEDATE);
    INSERT INTO BUGS(TITLE, SEVERITY, OPENDATE, CLOSEDATE) VALUES(TITLE, SEVERITY, OPENDATE, CLOSEDATE);
    SELECT @str as 'You Entered';
END $$
DELIMITER ;

Test:

CALL USER_INPUT('a',1,'2016-05-14');
+--------------------------------------------------+
| You Entered                                      |
+--------------------------------------------------+
| ABC||1||2016-06-28 17:05:58||2016-07-29 17:05:58 |
+--------------------------------------------------+