CODEWITHSUNDEEP
mysqlstored-procedures
Phil
Phil

Reputation: 4069

MySQL Stored Procedure Cursor based on parameters

I'm not very familiar with MySQL stored procedures, but am attempting to write one for the first time. In my procedure, I have 2 in parameters and one or both of them could be null. I need to create a cursor to loop over, but my cursor needs to be based on the in parameters. Where if 1 is null and the other isn't, my cursor query is different.

For example:

CREATE PROCEDURE test (IN date timestamp, IN id int(11))
BEGIN
     DECLARE cur CURSOR FOR 
     IF timestamp IS NOT NULL THEN
          IF id IS NULL THEN
               SELECT columns FROM Table WHERE modified_on <= timestamp
          ELSE
               SELECT columns FROM Table WHERE userid = id AND modified_on <= timestamp
     ELSE
          /* Logic here to pull query without the timestamp and where userid matches if the id passed in is not null */
     END IF
END

Could someone show me a simple example of how to achieve this?

Upvotes: 1

Views: 7719

Answers (1)

amdixon
amdixon

Reputation: 3833

issue

  • syntax error, the declare cursor statement requires to be associated with exactly one select query :

    DECLARE cursor_name CURSOR FOR select_statement

  • also Table is a reserved keyword and needs to be escaped ( or use a different name ) in :

    SELECT columns FROM Table

to fix, either create two cursors one for each scenario or embed both query paths in one select query

setup

create table `Table`
(
  id integer primary key auto_increment not null,
  userid integer not null,
  modified_on datetime not null
);

fix

-- option one : combine queries into one
drop procedure if exists test;
delimiter $$
CREATE PROCEDURE test (IN date timestamp, IN id int(11))
BEGIN
     DECLARE cur CURSOR FOR SELECT columns FROM `Table` WHERE ( id is null or userid = id ) and modified_on <= timestamp;
     -- open and read from cursor..
END$$
delimiter ;

-- option two define two cursors and use conditional logic below to decide which to read from
drop procedure if exists test;
delimiter $$
CREATE PROCEDURE test (IN date timestamp, IN id int(11))
BEGIN
     DECLARE cur1 CURSOR FOR SELECT columns FROM `Table` WHERE modified_on <= timestamp;
     DECLARE cur2 CURSOR FOR SELECT columns FROM `Table` WHERE userid = id AND modified_on <= timestamp;  
     -- evaluate if/else logic here to decide which cursor to open and use..
END$$
delimiter ;

note: not sure what you're planning todo for each cursor fetch. depending on your use case, its possible you can do this without a cursor. if this is the case, dont use a cursor and keep the processing closer to the natural sql set-based processing

reference

Upvotes: 2

Related Questions