CODEWITHSUNDEEP
mysqlstored-procedures
carol1287
carol1287

Reputation: 395

Is it possible to pass a variable to a stored procedure call in mysql?

I am trying to do the following and I am wondering if that is possible in MySQL:

CREATE PROCEDURE `sp_test`(
  ulon int(4),
  usrname varchar(20),
  usrst varchar(20),
  usrdt varchar(10)
)
BEGIN
  DECLARE bid int(11);
START TRANSACTION; 
  SELECT t_bid INTO bid FROM generalb WHERE dt=usrdt;
INSERT 
  INTO 
    rslt
    (
       rbid,
       rusrname,
       rusrst,
       usrdt 
    ) 
  VALUES
    (
      bid,
      usrname,
      usrst,
      usrdt
    );
    call sp_nextproc_13(bid, ulon);
COMMIT;
END;

Look at this line:

call sp_nextproc_13(bid, ulon);

How could I achieve something like this:

call sp_nextproc_@bid(bid, ulon);

I need to be able to call a procedure dynamically as I do not know the name of the procedure until I get the bid. If anyone knows the answer, thanks so much

LATEST UPDATE:

I have made the following changes:

CREATE PROCEDURE `sp_test`(
      ulon int(4),
      usrname varchar(20),
      usrst varchar(20),
      usrdt varchar(10)
    )
    BEGIN
      DECLARE bid int(11);
    START TRANSACTION; 
      SELECT t_bid INTO bid FROM generalb WHERE dt=usrdt;
    INSERT 
      INTO 
        rslt
        (
           rbid,
           rusrname,
           rusrst,
           usrdt 
        ) 
      VALUES
        (
          bid,
          usrname,
          usrst,
          usrdt
        );

        SET @sql=concat('call sp_nextproc_',bid,'(?,?)');
        PREPARE stmt FROM @sql;
        SET @var1=bid;
        SET @var2=ulon;
        EXECUTE stmt USING @var1, @var2;
    COMMIT;
    END;

IF I hardcode some values, it works. However if I don't, it doesn't. Please see below:

If I call:

CALL sp_test(2, 'John','test','AAAA');

ERROR here:

SELECT t_bid INTO bid FROM generalb WHERE dt=usrdt;

Column t_bid cannot be null

If I hardcode the value like this:

SELECT t_bid INTO bid FROM generalb WHERE dt='AAAA';

The error disappears.

The second error is at the following line:

        SET @var1=bid;
        SET @var2=ulon;
        EXECUTE stmt USING @var1, @var2;

ERROR missing value @var1 and @var2.

If I hardcode var1 and var2, it works:

        SET @var1=13;
        SET @var2=2;
        EXECUTE stmt USING @var1, @var2;

I tried using @ and without it on the variables but that didn't work. What I am doing wrong? :(

Upvotes: 0

Views: 114

Answers (1)

Drew
Drew

Reputation: 24959

    delimiter $$
    create procedure sp_test
    (
    ulon int(4)
    )
    BEGIN
        DECLARE bid int(11);
        set bid=8888;
...
...
...
...
        set @sql=concat('call sp_nextproc_',bid,'(?,?)');
        prepare stmt from @sql;
        set @var1=bid;
        set @var2=ulon;
        EXECUTE stmt using @var1,@var2;
    END
    $$
    -- ------------
    delimiter $$
    CREATE PROCEDURE `sp_nextproc_8888`(
    IN bid int(11),
    IN ulon int(4)
    )
    BEGIN
        select bid,ulon;
    END
    $$

-- test with these:
-- call sp_test(9999);
-- call sp_nextproc_8888(111,222);

Upvotes: 1

Related Questions