|
Hi,
Why I am getting the above error for this stored procedure?
CREATE DEFINER=`root`@`192.168.1.%` PROCEDURE `sp_open_file`(IN param_file_no int, IN param_clinic_id int, IN param_doctor_id int, IN param_account_category int, IN param_account_id int)
BEGIN
DECLARE param_patient_account_number BIGINT;
DECLARE param_price_list_id int;
DECLARE param_account_number BIGINT;
SET param_patient_account_number = (SELECT account_number FROM patients WHERE file_no = param_file_no);
IF (param_account_category = 1) THEN
SET param_account_number = param_patient_account_number;
ELSE
SET param_account_number = (SELECT account_number FROM accounts WHERE account_id = param_account_id);
END IF;
SET param_price_list_id = (SELECT price_list_id FROM accounts WHERE account_number = param_account_number);
INSERT INTO visits (file_no, clinic_id, doctor_id, account_category, account_number, price_list_id) VALUES (param_file_no, param_clinic_id, param_doctor_id, param_account_category , param_account_number, param_price_list_id);
UPDATE patients SET file_is_open = TRUE;
END
Technology News @ www.JassimRahma.com
|
|
|
|
|
..because one of the three subqueries returns more than one row.
(SELECT account_number FROM patients WHERE file_no = param_file_no);
(SELECT account_number FROM accounts WHERE account_id = param_account_id);
(SELECT price_list_id FROM accounts WHERE account_number = param_account_number);
Meaning that the table holds more than a single record with the requested Id.
|
|
|
|
|
Quote: Meaning that the table holds more than a single record with the requested Id.
True, this is the main reason for such errors. Many resolution are available for this errors.
1) Use Top 1 keyword i.e.
(SELECT TOP 1 account_number FROM patients WHERE file_no = param_file_no);
(SELECT TOP 1 account_number FROM accounts WHERE account_id = param_account_id);
(SELECT TOP 1 price_list_id FROM accounts WHERE account_number = param_account_number);
2) Some of your data require 1 or more condition (filter) in your sql query.
3) If any duplicate entry found, Please remove.
Maulik Dusara
Sr. Sofware Engineer
|
|
|
|
|
MaulikDusara wrote: True, this is the main reason for such errors. Many resolution are available for this errors.
1) Use Top 1 keyword i.e.
That's assuming that there's more of those records allowed in the table, and being an "Id", this COULD lead to an inconsistent database - exaggerating the problem.
|
|
|
|
|
I agree with Eddy, while top 1 fixes this problem it ignores the underlying data problem that the user was expecting 1 record per ID and is not getting the correct result.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Quote: not getting the correct result.
I also agree and I also mentioned that Quote: Many resolution are available for this errors
Please also check my other resolutions.
Maulik Dusara
Software Engineer/Team Leader
|
|
|
|
|
MaulikDusara wrote: Please also check my other resolutions.
That's the point; he should not "try resolutions", but analyze the bug; if there are multiple records coming back on a key-field selection, chances are that there's an inconsistency. Selecting the "top 1" might hide that problem a little longer.
|
|
|
|