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?

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 |
+------+
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.