CODEWITHSUNDEEP
mysqlstored-proceduresmysql-workbench
Niraj
Niraj

Reputation: 517

Why is my procedure producing null result?

I have simply created procedure which take 2 inputs and give 2 outputs.
When I run query separately it gives me proper output, But If I will try to call procedure It produce me null result

while calling Query:

SELECT field1, field2 
        INTO @var1, @var2
        FROM MyTable ID=? and Name=?

and then :

select  @var1, @var2;

But If I combine the same code in procedure it will give null for both fields.

Procedure: 

DELIMITER $$

CREATE DEFINER=`user`@`localhost` PROCEDURE `My_Proc`(
    IN `ID` INT,
    IN `Name` VARCHAR(20)
    -- OUT `result` INT,
    -- OUT `result1` INT
)
BEGIN

 set @var1 = 0;
 set @var2 = 0;

    set @query := CONCAT("SELECT
                      field1, field2 
             INTO @var1, @var2
                      FROM MyTable ID=? and Name=?");

    PREPARE stmt FROM @query;
    Execute stmt USING @Id,@Name;
    DEALLOCATE PREPARE `stmt`;

select @var1,@var2;


END

I tried to call select @var1,@var2; outside procedure but it also give null fields.

I am working in Mysql Workbench 6.0

Upvotes: 0

Views: 52

Answers (1)

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

you have some mistakes in your procedure thats have been removed below..........have a look 

DELIMITER $$

CREATE DEFINER=`user`@`localhost` PROCEDURE `My_Proc`(
    IN `ID` INT,
    IN `Name` VARCHAR(20)
    -- OUT `result` INT,
    -- OUT `result1` INT
)
BEGIN

 set @var1 = 0;
 set @var2 = 0;

    set @query := CONCAT("SELECT
                      field1,field2 
             INTO @var1, @var2
                      FROM MyTable where ID=? and Name=?");

    PREPARE stmt FROM @query;
    Execute stmt USING @Id,@Name;
    DEALLOCATE PREPARE stmt ;

select @var1,@var2;


END

Upvotes: 1

Related Questions