Ad

MySQL Stored Procedure Returning All Results Ignoring WHERE Claus

- 1 answer

I have two stored procs which i call from my laravel application.

My laravel application passes in a cID parameter which is then passed to the stored procedure as the "where clause". But it seems something is going astray and possibly my variables arent set up properly.

Also i know that laravel IS passing the correct cID to my stored proc because i enabled the logs for mysql to see if it was passing any params.

Also the stored procedure select statement runs fine as a query if i manually set the ClientID = '';

My stored proc sends ALL clients and cards to the view, totally ignoring the where clause.

Laravel code:

Route::get('/clients/{cID}', function ($cID) {

    $details = DB::select('CALL sp_Details(' . DB::raw($cID) . ')');

    $cards = DB::select('CALL sp_Cards(' . DB::raw($cID) . ')');

   return view('client.show', compact('details','cards'));

});

Any my Stored Proc

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Details`(IN cID int )
BEGIN

    SET @ClientID = cID;

            SELECT 
               ClientID,
               Client_Name
            FROM accounts
            where @ClientID = cID;
  END

Stored Proc #2

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Cards`(cID int)
BEGIN

    SET @ClientID = cID;

          SELECT 
          ClientID,
          Code
      FROM cards
      where cID = @ClientID;
    END
Ad

Answer

You are using local variables like @ClientID and you are confusing it to column names, must change your code to avoid them and there is no necessary use the local variable:

CREATE PROCEDURE `sp_Details`(IN cID int )
BEGIN

        SELECT 
           ClientID,
           Client_Name
        FROM accounts
        where ClientID = cID;
END

The other proc:

CREATE PROCEDURE `sp_Cards`(cID int)
BEGIN

  SELECT 
      ClientID,
      Code
  FROM cards
  where ClientID = cID;
END
Ad
source: stackoverflow.com
Ad