CODEWITHSUNDEEP
mysqlstored-procedures
Joshua M. Moore
Joshua M. Moore

Reputation: 399

stuck on a stored procedure

I have a query that works just fine in the latest mysql. 

select Title, Link
from PlaylistItem
where PlaylistId = 1;

which returns:

+---------------------------+---------------------------------------------+
| Title                     | Link                                        |
+---------------------------+---------------------------------------------+
| Regina Spektor - Fidelity | https://www.youtube.com/watch?v=wigqKfLWjvM |
+---------------------------+---------------------------------------------+

But when I write a stored procedure, I get an empty result set.

The stored procedure looks like this: 

/* Fetch all playlist items for a playlist */
delimiter //
create procedure MixtapeDating.GetPlaylist
(
    in Id int
)
begin
    select Id, Title, Link
    from PlaylistItem
    where PlaylistId = @Id; 
end //
delimiter ;

I run the procedure like thus:

call GetPlaylist(1);

Upvotes: 0

Views: 82

Answers (1)

Shadow
Shadow

Reputation: 34231

In mysql variables prefixed by @ are session variables, not stored procedure level variables, so @id is not the same as id. Moreover, id is also a fieldname, so you must rename your parameter.

delimiter //
create procedure MixtapeDating.GetPlaylist
(
    in var_Id int
)
begin
    select Id, Title, Link
    from PlaylistItem
    where PlaylistId = var_Id; 
end //
delimiter ;

Upvotes: 1

Related Questions