0

I needed to fetch the points for a Building at a certain level. For this, I created a Stored procedure in MySQL Workbench 5.2 CE as following:

CREATE DEFINER=`tribalwars`@`%` PROCEDURE `GetPoints`(IN BuildingID INT, IN BuildingLevel INT, OUT Pts INT)
BEGIN
    Select Points
    INTO Pts
    From  Points
    Where BID = @BuildingID AND bLevel = @BuildingLevel;
END

When I execute the following command I get either a NULL or BLOB entry:

CALL GetPoints(1,1,@pts);
Select @pts;

My Table that I'm querying is as follows:

Field	Type	Null	Key	Default	Extra
BID	int(11)	NO	PRI	NULL	
bLevel	int(11)	NO	PRI	NULL	
Points	int(11)	NO		NULL

can someone please tell me what I'm missing or doing wrong?

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by smantscheff
0

Drop the @ variable marker from your procedure:

drop table if exists points;
create table points (
BID	int(11) not null,
bLevel int(11) not null,
Points	int(11)	not null);
insert into points values (1,1,10);
drop procedure `GetPoints`;
delimiter //
CREATE procedure `GetPoints`(IN BuildingID INT, IN BuildingLevel INT, OUT Pts INT)
BEGIN
    Select Points
    INTO Pts
    From  Points
    Where BID = BuildingID AND bLevel = BuildingLevel;
END //
delimiter ;

set @pts = 9;
CALL GetPoints(1,1,@pts);
Select @pts;

+------+
| @pts |
+------+
|   10 |
+------+
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.