Laravel调用存储过程Sp_Available_Rooms报错求助

That error message spells out the exact problem: your Sp_Available_Rooms stored procedure expects 1 argument, but you’re calling it with 0. Let’s walk through how to resolve this step by step:

1. First, confirm what parameter the procedure requires #

Start by checking the full definition of your stored procedure in MySQL. Run this query to get all details about its parameters:

SHOW CREATE PROCEDURE clickgo.Sp_Available_Rooms;

This will show you the parameter’s name, data type, and whether it’s an IN, OUT, or INOUT parameter (for a room availability query, it’s most likely an IN parameter like a check-in date, room type ID, or date range).

2. Update your Laravel call to pass the required parameter #

Once you know what parameter is needed, adjust your PHP code to include it. For example, if the procedure expects a check_in date parameter:

// Replace with your actual date value (e.g., user-selected check-in date)
$checkInDate = '2024-05-15';
$roomtypes = DB::select("CALL Sp_Available_Rooms(?)", [$checkInDate]);

If it needs multiple parameters (like both check-in and check-out dates), add them in the correct order:

$checkInDate = '2024-05-15';
$checkOutDate = '2024-05-20';
$roomtypes = DB::select("CALL Sp_Available_Rooms(?, ?)", [$checkInDate, $checkOutDate]);

Using parameter binding (?) here is critical to avoid SQL injection risks.

3. Optional: Make the parameter optional (if you want no-argument calls to work) #

If you want the procedure to run without passing a parameter, you can modify it to set a default value. For example, if your original procedure was defined like this:

CREATE PROCEDURE Sp_Available_Rooms(IN check_in DATE)
BEGIN
  -- Your room availability logic here
END;

Update it to include a default value and handle the fallback case:

ALTER PROCEDURE Sp_Available_Rooms(IN check_in DATE DEFAULT NULL)
BEGIN
  -- Use today's date if no check-in date is provided
  IF check_in IS NULL THEN
    SET check_in = CURDATE();
  END IF;
  -- Your existing room availability logic here
END;

Now your original call CALL Sp_Available_Rooms() will work, as the parameter will use its default value when not provided.

内容的提问来源于stack exchange，提问作者user9728944