8

I have a sequence table with two columns, name, value, and I have a stored procedure for incrementing the value provided the name

DROP PROCEDURE IF EXISTS p_generate_sequence;
delimiter |
CREATE PROCEDURE p_generate_sequence (name VARCHAR(30))
BEGIN
    START TRANSACTION;
    -- Variable "name" equal to column "name", how to reference?
    UPDATE sequences_table SET value = value + 1 WHERE name = name;
    SELECT value FROM sequences_table WHERE name = name;
    COMMIT;
END |
delimiter ;

Note the parameter is called "name".

Is there any approach for using a parameter with the same name as the column name of the affected table?

NOTE: I'm not interested in change parameter's name, nor even column's name, just to know whether is it possible or not, and how.

Jhonny D. Cano -Leftware-
  • 17,663
  • 14
  • 81
  • 103

1 Answers1

10

Yes, scope your table column by an alias.

e.g.

delimiter //
create procedure foo( id int )
begin
 select * from users u where u.id = id;
end
//

call foo( 123 )

returns user id = 123

Matt Rogish
  • 24,435
  • 11
  • 76
  • 92